x

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!

more ▼

asked Mar 10, 2011 at 06:37 AM in Default

avatar image

tabularyee
160 11 12 17

Why do you want the date in yyyyddmm format? or is that a typo?

Mar 10, 2011 at 06:52 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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?

more ▼

answered Mar 10, 2011 at 06:46 AM

avatar image

Magnus Ahlkvist
21.5k 19 39 42

Now that's the big question...

Mar 10, 2011 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, 2011 at 07:13 AM tabularyee

You have my profound sympathies.

Mar 10, 2011 at 01:11 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
 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,...)

more ▼

answered Mar 10, 2011 at 07:06 AM

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

(comments are locked)
10|1200 characters needed characters left

or you could just use

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

more ▼

answered Mar 10, 2011 at 06:45 AM

avatar image

Mrs_Fatherjack
5.1k 64 66 76

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

Mar 10, 2011 at 06:51 AM ThomasRushton ♦♦

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

Mar 10, 2011 at 06:59 AM Mrs_Fatherjack

...that too.

I'm not having a good day today.

Mar 10, 2011 at 07:01 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

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

answered Mar 10, 2011 at 07:02 AM

avatar image

Mrs_Fatherjack
5.1k 64 66 76

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, 2011 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, 2011 at 07:05 AM Magnus Ahlkvist

damn, that's not good then.

Mar 10, 2011 at 07:06 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x31

asked: Mar 10, 2011 at 06:37 AM

Seen: 4466 times

Last Updated: Mar 10, 2011 at 06:37 AM

Copyright 2016 Redgate Software. Privacy Policy