Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert DateTime to MMDDYY Expand / Collapse
Author
Message
Posted Thursday, January 07, 2010 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 2:47 PM
Points: 3, 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
Post #843738
Posted Thursday, January 07, 2010 11:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #843741
Posted Thursday, January 07, 2010 11:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 27, 2010 11:01 AM
Points: 47, 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?
Post #843752
Posted Thursday, January 07, 2010 11:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:12 PM
Points: 2,132, Visits: 3,399
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.
Post #843753
Posted Thursday, January 07, 2010 11:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
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.





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)
Post #843757
Posted Thursday, January 07, 2010 11:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:06 PM
Points: 6,924, Visits: 12,636
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
Post #843786
Posted Thursday, January 07, 2010 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 05, 2011 2:47 PM
Points: 3, Visits: 101
Thank you! That worked!
Phillip
Post #843787
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse