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

tabularyee gravatar image

tabularyee
160 11 12 14

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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

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

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(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

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

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

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1943
x43

asked: Mar 10, 2011 at 06:37 AM

Seen: 3376 times

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