SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert DateTime to MMDDYY


Convert DateTime to MMDDYY

Author
Message
pgatte
pgatte
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 101
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
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55359 Visits: 9730
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
jcdyntek
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 130
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
Ray K
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6574 Visits: 4676
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
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91186 Visits: 38945
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22701 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
pgatte
pgatte
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 101
Thank you! That worked!
Phillip
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search