question

tabularyee avatar image
tabularyee asked

getDate() code for yyyyddmm

Is there a code to get the date format yyyyddmm using the convert function i.e, SELECT CONVERT(varchar,GETDATE(), desired format here ) Thanks!
sql-server-2005date-functions
1 comment
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Why do you want the date in yyyyddmm format? or is that a typo?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
For YYYYDDMM you won't have much help from CONVERT. You can get YYYYMMDD. To use the fewest number of function calls, I'd go for: SELECT SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,4) + SUBSTRING(CONVERT(varchar(8),GETDATE(),112),7,2) + SUBSTRING(CONVERT(varchar(8),GETDATE(),112),5,2) Out of curiuosity: Why do you want it on YYYYDDMM format?
3 comments
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Now that's the big question...
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
You have my profound sympathies.
1 Like 1 ·
tabularyee avatar image tabularyee commented ·
I know, it was set by the creators of the database I'm working on which also is barely normalised.
0 Likes 0 ·
Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
or you could just use > SELECT CONVERT(varchar(10), getdate(),112)
3 comments
10 |1200

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

Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
and I didn't read the question properly that brings back YYYYMMDD
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
d'oh. comes of me not reading the documentation properly...
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
...that too. I'm not having a good day today.
0 Likes 0 ·
Mrs_Fatherjack avatar image
Mrs_Fatherjack answered
Would this do it? SELECT CAST( CAST(DATEPART(yyyy,GETDATE()) AS VARCHAR(4)) + CAST(DATEPART(dd,GETDATE()) AS VARCHAR(2)) + CAST(DATEPART(mm,GETDATE()) AS VARCHAR(2)) AS VARCHAR(8))
3 comments
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I already tried something very similar... datepart(dd...) and datepart(mm...) lie, and only return 1 character if they can get away with it. At least they do on SQL 2005...
1 Like 1 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
damn, that's not good then.
1 Like 1 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
No, unfortunately not. It would give you YYYYDM for January 1st, but YYYYDDMM for December 31st. I think my suggested solution is the one with the least number of function calls possible. But if it's the most effective solution, that I don't know - it depends on how effective different string manipulation functions and date functions are.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
SELECT convert(char(4), datepart(yyyy, getdate())) + right('0' + convert(varchar(2), datepart(dd, getdate())),2) + right('0' + convert(varchar(2), datepart(mm, getdate())),2) In response to @Mrs_Fatherjack's attempt using `datepart(dd,...)`
10 |1200

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

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.