Convert DateTime to MMDDYY

Question

Convert DateTime to MMDDYY

pgatte

Old Hand

Points: 365
More actions
January 7, 2010 at 11:05 am

#214328

I am new to SQL.
Can you tell me how I would convert a DateTime field to MMDDYY without any / or .
Example I would need the field to convert to 10409 for January 1, 2009.
Any help would be greatly appreciated.
Thanks
Phillip Gatte

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You could convert using style 10, then wrap that in a replace that gets rid of the punctuation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jcdyntek Ten Centuries Points: 1153 More actions · Answer 2

SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]]

you would need to replace GETDATE() function with your date field. This will display as

010710.

Are the leading zeros a problem?

Ray K SSC-Dedicated Points: 31164 More actions · Answer 3

pgatte (1/7/2010)
I am new to SQL.
Can you tell me how I would convert a DateTime field to MMDDYY without any / or .
Example I would need the field to convert to 10409 for January 1, 2009.

Just checking: how do you get 10409 from Jan. 1 2009? (I'm assuming this is a typo.)

Also, don't underestimate fixed characters for dates. 10409 can be interpreted as either Jan. 4, 2009 (1/04/09) or Oct. 4, 2009 (10/4/09). I'd suggest including a leading zero so it reads 010409 (and is consistent with the MMDDYY format). That way, there's no confusion.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Ray K (1/7/2010)
pgatte (1/7/2010)
I am new to SQL.
Can you tell me how I would convert a DateTime field to MMDDYY without any / or .
Example I would need the field to convert to 10409 for January 1, 2009.
Just checking: how do you get 10409 from Jan. 1 2009? (I'm assuming this is a typo.)
Also, don't underestimate fixed characters for dates. 10409 can be interpreted as either Jan. 4, 2009 (1/04/09) or Oct. 4, 2009 (10/4/09). I'd suggest including a leading zero so it reads 010409 (and is consistent with the MMDDYY format). That way, there's no confusion.

Even then, if you don't know it is stored as MMDDYY, 010409 could still be confused as Jan 4, 2009 or Apr 1, 2009. Just something to be aware of depending on who will be seeing the data. You may want to be sure this is documented appropriately.

LutzM SSC Guru Points: 107049 More actions · Answer 5

To even confuse a little more, 010409 might be as well interpreted as April 9th 2001...

I probably would even include the format in the string and truncate it where needed (e.g. 010409MDY). I don't expect it to be used in any calculation...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pgatte Old Hand Points: 365 More actions · Answer 6

pgatte

Old Hand

Points: 365

January 7, 2010 at 11:59 am

#1100422

Thank you! That worked!

Phillip