How to flip month and day in a datetime? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to flip month and day in a datetime?

Author

Topic

omnri
Starting Member

5 Posts

Posted - 2012-04-19 : 05:08:45

How can I flip "2012-12-01 12:33:00.0" to become "2012-01-12 12:33:00.0"? I had tried "select convert(varchar(50), convert(datetime, log_date, 103), 121)" and used both 101 and 103 and still not able to flip it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-19 : 07:23:20

I am assuming that what you mean is that you want to format the date of December 1, 2012 to the form "2012-01-12 12:33:00.0". There does not seem to be a built-in style that would do that. The available styles are listed here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Your alternatives would be:

a) Do the "flipping" yourself using string manipulation. Not too hard, if you must do it.
b) Do the formatting on the client (such as a front-end application).

Most experts on this forum would advise you to go with option B and would advise against option A.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-19 : 10:55:28

I'm not sure if the date is a string or an actual date(time) datatype. But, maybe setting the DATEFORMAT will help?

SET DATEFORMAT YDM

SELECT 
	CAST('2012-12-01 12:33:00.0' AS DATETIME),
	CONVERT(VARCHAR(25), CAST('2012-12-01 12:33:00.0' AS DATETIME), 126)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-04-19 : 11:40:53

quote:
Originally posted by Lamprey

I'm not sure if the date is a string or an actual date(time) datatype. But, maybe setting the DATEFORMAT will help?
SET DATEFORMAT YDM

SELECT
CAST('2012-12-01 12:33:00.0' AS DATETIME),
CONVERT(VARCHAR(25), CAST('2012-12-01 12:33:00.0' AS DATETIME), 126)

That isn't doing what you think; it's converting the input date format, not the output date format.

Here is the same code modified to show what is happening.

set nocount on
set dateformat ydm

declare @dt1 datetime
declare @dt2 datetime

-- Universal, unambiguous date format of YYYYMMDD
set @dt1 = '20121201 12:33:00.0'
-- Ambiguous date format that depends on setting of dateformat
set @dt2 = '2012-12-01 12:33:00.0'

select [@dt1] =@dt1
select [@dt2] =@dt2

select 
	DC1 = convert(varchar(25),@dt1, 126),
	DC2 = convert(varchar(25),@dt2, 126)

select 
	DS1 = convert(varchar(25),@dt1, 113),
	DS2 = convert(varchar(25),@dt2, 113)

Results:

@dt1                                                   
------------------------------------------------------ 
2012-12-01 12:33:00.000

@dt2                                                   
------------------------------------------------------ 
2012-01-12 12:33:00.000

DC1                       DC2                       
------------------------- ------------------------- 
2012-12-01T12:33:00       2012-01-12T12:33:00

DS1                       DS2                       
------------------------- ------------------------- 
01 Dec 2012 12:33:00:000  12 Jan 2012 12:33:00:000

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-19 : 12:16:41

quote:
Originally posted by Michael Valentine Jones

That isn't doing what you think; it's converting the input date format, not the output date format.

No, it's doing exactly what I think. I'm just not sure what the OP is actually trying to do, so I was offering something that might help depending on the actual scenario. ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources