getDate() code for yyyyddmm

Question

Is there a code to get the date format yyyyddmm using the convert function

i.e,

SELECT CONVERT(varchar,GETDATE(), desired format here )

Thanks!

Answer 1

0

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?

answered Mar 10 '11 at 06:46 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

Now that's the big question...

Mar 10 '11 at 06:52 AM ThomasRushton ♦

I know, it was set by the creators of the database I'm working on which also is barely normalised.

Mar 10 '11 at 07:13 AM tabularyee

You have my profound sympathies.

Mar 10 '11 at 01:11 PM ThomasRushton ♦

(comments are locked)

Answer 2

0

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,...)

answered Mar 10 '11 at 07:06 AM

ThomasRushton ♦
29.2k ● 6 ● 9 ● 36

(comments are locked)

Answer 3

0

or you could just use

SELECT CONVERT(varchar(10), getdate(),112)

answered Mar 10 '11 at 06:45 AM

Mrs_Fatherjack
4.6k ● 43 ● 52 ● 60

d'oh. comes of me not reading the documentation properly...

Mar 10 '11 at 06:51 AM ThomasRushton ♦

and I didn't read the question properly that brings back YYYYMMDD

Mar 10 '11 at 06:59 AM Mrs_Fatherjack

...that too.

I'm not having a good day today.

Mar 10 '11 at 07:01 AM ThomasRushton ♦

(comments are locked)

Answer 4

0

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))

answered Mar 10 '11 at 07:02 AM

Mrs_Fatherjack
4.6k ● 43 ● 52 ● 60

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...

Mar 10 '11 at 07:05 AM ThomasRushton ♦

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.

Mar 10 '11 at 07:05 AM Magnus Ahlkvist

damn, that's not good then.

Mar 10 '11 at 07:06 AM Mrs_Fatherjack

(comments are locked)

getDate() code for yyyyddmm

Follow this question

Related Questions