Datetime conversion

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kev Riley » Datetime conversion

Datetime conversion

Rate Topic

Display Mode

Topic Options

Author

Message

kevriley

Posted Thursday, May 22, 2008 9:06 PM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

Comments posted to this topic are about the item Datetime conversion

Post #505599

Yelena Varshal

Posted Friday, May 23, 2008 9:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

What is interesting, that if you don't convert to the character string, the format is different:

set language us_english
set dateformat dmy
go
declare @date datetime
set @date = '11 apr 2008 17:10'
select left(@date,1)
select @date, convert(varchar(20), @date)

returns:
A
2008-04-11 17:10:00.000 Apr 11 2008 5:10PM

Regards,
Yelena Varshal

Post #505953

kevriley

Posted Friday, May 23, 2008 9:40 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:01 PM
Points: 2,677, Visits: 2,273

Yes - that shows the difference between the default conversion format and the default display format. I've seen many people get caught out by that before, expecting that left(@date,1) = '2'....hmm maybe I should have used that as one of the wrong answers....

Post #505977

Tom Garth

Posted Friday, May 23, 2008 2:32 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

Yes. 2 should have been an option. I would have gotten it wrong then instead of testing it to get it right.

Nice QoD. We are destined to constantly forget the simplest of things.

And...

select @date, convert(varchar(20), @date)

is the same as

select @date, convert(varchar(20), @date, 0)

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Post #506142

Anipaul

Posted Saturday, May 24, 2008 7:51 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:50 AM
Points: 4,785, Visits: 1,334

Nice question......

Post #506240

Terry Maher

Posted Monday, May 26, 2008 6:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 5:56 AM
Points: 79, Visits: 45

This is probably a stupid question, but what controls the formatting of a datetime column with no cast/convert (in SQL query analyzer or management studio)?

Post #506470

Yelena Varshal

Posted Wednesday, May 28, 2008 11:51 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519

Either Microsoft Programmers or Defaults.
In the CAST and CONVERT (TSQL) BOL article there is a table with formats. My date is returned as the following if Management Studio query window withput formatting:

2000-04-06 18:06:31.000

according to the table, the format name and description for that are:
ODBC canonical (with milliseconds)
yyyy-mm-dd hh:mi:ss.mmm(24h)

It is not just default:
Default mon dd yyyy hh:miAM (or PM)

My assumption that it may be a front end (Management Studio) programmers formatted it this way because of 2 things:
1. When I use Profiler to profile running a query in Management Studio, the Profiler shows the original query, so the formatting is done in the front end.
2. Linked Server to Oracle query returnes dates /times in the same format as when running a query towards SQL Server.

Regards,
Yelena Varshal

Post #507902