Convert Not returning correct date format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Not returning correct date format

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2010-07-14 : 12:20:45

Hi Folks,

When I use the following syntax

select  convert(datetime, '01-12-2010',105)

I get

2010-12-01 00:00:00.000

while I want it to return

01-12-2010 00:00:00.000

Any help?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-14 : 13:15:33

This is a display issue. Thes style 105 applies to convert a datetime to a varchar, not vice versa.

set dateformat dmy
select convert(varchar(20),convert(datetime, '01-12-2010'),105)

Jim

Everyday I learn something that somebody else already knew

dattatreysindol
Starting Member

20 Posts

Posted - 2010-07-14 : 14:27:31

Hi There -

Check this article on Books Online.
http://msdn.microsoft.com/en-us/library/ms189491.aspx

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 14:34:49

quote:
Originally posted by jimf

Thes style 105 applies to convert a datetime to a varchar, not vice versa

Don't think that's the case Jim.


SELECT CONVERT(datetime, '01/02/03', 1)	-- mm/dd/yy
SELECT CONVERT(datetime, '01/02/03', 3)	-- dd/mm/yy

but DATEFORMAT dmy is probably a better bet - even better, IMHO, is to only ever pass dates as text strings in the format "yyyymmdd" (NO hyphens) or "yyyy-mm-ddThh:mm:ss.sss" (hyphens REQUIRED!) both of which are treated by SQL as being unambiguous WHATEVER the DATEFORMAT setting is


SELECT CONVERT(datetime, '20030201', 1)
SELECT CONVERT(datetime, '20030201', 3)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-14 : 15:27:38

Yoicks! I even tested one. I should never volunteer unrequested information, especially when it's wrong! But this breaks
SELECT CONVERT(datetime, '01/02/2003', 1)

which is why I am finally getting in the habit of
"yyyymmdd" (plus you there's less possibility of me typing
'030/1/2003'

Thanks,
Jim

Everyday I learn something that somebody else already knew

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 02:05:47

". I should never volunteer unrequested information"

hehehe ... although perhaps the best way to learn. Somewhere in this forum I said exactly the same thing, and Nigel put me straight )(see I still remember that bit even ...)

SELECT CONVERT(datetime, '01/02/2003', 1)

would be wrong because it included a century, therefore you need a "style" of 101, rather than 1.

I only ever use yyyymmdd too now - except perhaps for some sort of data import-cleanup (but then I use DATEFORMAT). Pity Sybase (period to Microsoft) ever added the "Any date format will do, we'll try to convert it, even if its ambiguous"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources