Prev: RPC vs. RPC Out for Linked Servers
Next: Fragmentation
From: Massimo on 12 Feb 2010 08:36
Hi,
why

SELECT CAST('2009-02-12 00:00:00:000' AS DATETIME)

returns '2009-12-02 00:00:00.000'.

This drive me crazy: can you help me?

Thanks in advance.

Massimo
From: Hugo Kornelis on 12 Feb 2010 08:48
On Fri, 12 Feb 2010 05:36:11 -0800 (PST), Massimo wrote:

>Hi,
>why
>
>SELECT CAST('2009-02-12 00:00:00:000' AS DATETIME)
>
>returns '2009-12-02 00:00:00.000'.
>
>This drive me crazy: can you help me?
>
>Thanks in advance.
>
>Massimo

Hi Massimo,

What output do you expect and why?

Note that this statement will convert the character representation
'2009-12-02 00:00:00.000' to the internal representation of the datetime
value that corresponds to midnight of February 12th, 2009. This value is
then returned to the client, and the client displays it in a user- and
locale-dependant format.

(If you run SET LANGUAGE us_english before running this statement,
you'll get '2009-02-12 00:00:00.000' returned instead)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Jay on 12 Feb 2010 09:11
Does this help?

DECLARE @testtab TABLE (
col1 DATETIME
);
INSERT INTO @testtab
SELECT CAST('2009-02-12 00:00:00:000' AS DATETIME)
GO

DECLARE @testtab TABLE (
col1 CHAR(23)
);
INSERT INTO @testtab
SELECT CAST('2009-02-12 00:00:00:000' AS CHAR)
GO

"Massimo" <massimo.petrillo(a)gmail.com> wrote in message
news:752b4210-8885-4651-9dd4-713bca8e0b87(a)q16g2000yqq.googlegroups.com...
> Hi,
> why
>
> SELECT CAST('2009-02-12 00:00:00:000' AS DATETIME)
>
> returns '2009-12-02 00:00:00.000'.
>
> This drive me crazy: can you help me?
>
> Thanks in advance.
>
> Massimo


From: Dan Guzman on 12 Feb 2010 09:50
> why
>
> SELECT CAST('2009-02-12 00:00:00:000' AS DATETIME)
>
> returns '2009-12-02 00:00:00.000'.

Interpretation of ambiguous date/time strings depends on the current session
DATEFORMAT setting, which is initially determined by the login's language.
Use an ISO or ISO8601 date/time string format to avoid this issue:

SELECT CAST('20090212' AS DATETIME) --ISO
SELECT CAST('2009-02-12T00:00:00.000' AS DATETIME) --ISO8601

Also, be aware that the formatting of result data is determined the the
client application, not by SQL Server. Native datetime data is in SQL
Server and has no format.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


From: Plamen Ratchev on 12 Feb 2010 10:12
In addition to the other comments, Tibor's article below provides detailed explanation of the date/time data types:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: RPC vs. RPC Out for Linked Servers
Next: Fragmentation