question

jhowe avatar image
jhowe asked

SQL convert ddmmyyyy to dd-mm-yyyy date format help

Hi all `SELECT CONVERT(DATE,STUFF(STUFF('051111',3,0, '-'), 6, 0, '-'))` 051111 is 5 Nov 2011 is giving me 2011-05-11 however i would like 05-11-2011 i can't seem to get sql to give me this. If i try to add a style in i get error Conversion failed when converting date and/or time from character string. i.e. `SELECT CONVERT(DATE,STUFF(STUFF('051111',3,0, '-'), 6, 0, '-'), 105)` (this should give me the correct ouptut but fails) thanks for your help!
sql-server-2008t-sqlsql-server-2008-r2
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Kev Riley avatar image
Kev Riley answered
Convert the result of your first conversion, not before... `SELECT convert(varchar,CONVERT(DATE,STUFF(STUFF('051111',3,0, '-'), 6, 0, '-')),110)` gives ------------------------------ 05-11-2011 (1 row(s) affected)
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
yes that's correct thanks!
0 Likes 0 ·
jhowe avatar image jhowe commented ·
if i change the date to 301111 for example this fails why is that? i think this needs tweaking slightly. anything over '121111' i.e. '131111' input will fail which suggests it is not outputting in ddmmyyyy format
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Seems like your instance is defaulting to mdy, so 30-11-11 is an invalid date (11th day of the 30th month) Use `set dateformat dmy` before the query and it will work
0 Likes 0 ·
jhowe avatar image jhowe commented ·
yes that's great. Fixed, i don't want it to default to MDY how do i permanently get it to use DMY? This is a UK install so i don't want it to be defaulting to american date format.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
You can set it at server level using sp_configure 'default language'.... for the logins or at session level using SET LANGUAGE - maybe something like `SET LANGUAGE 'British'` - this will set it to DMY, however, if you have any software installed that uses this instance and assumes a 'us_english' installation, you may cause yourself lots of headaches. The table sys.syslanguages lists the options
0 Likes 0 ·
Geeth avatar image
Geeth answered
select CONVERT(nvarchar(30), GETDATE(), 110)
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
this won't give me what i want. sorry it's dd-mm-yyyy i'm after this will just give me output of 051111
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.