SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Kev Riley / Datetime conversion / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:21:29 GMT20RE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxgood question but confusing one....Tue, 04 Dec 2012 00:27:20 GMTkapil_kkRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxBest QoD in a while. More like this, please!Wed, 28 May 2008 11:53:38 GMTMichael SpencerRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxEither 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.000according 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.Wed, 28 May 2008 11:51:54 GMTYelena VarshalRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxThis 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)?Mon, 26 May 2008 06:55:21 GMTTerry MaherRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxNice question......Sat, 24 May 2008 07:51:13 GMTAnipaulRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxYes. 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...[quote]select @date, convert(varchar(20), @date)[/quote] is the same as [quote]select @date, convert(varchar(20), @date, 0)[/quote]Fri, 23 May 2008 14:32:54 GMTTom GarthRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxYes - 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....Fri, 23 May 2008 09:40:30 GMTkevrileyRE: Datetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxWhat is interesting, that if you don't convert to the character string, the format is different:set language us_englishset dateformat dmygodeclare @date datetimeset @date = '11 apr 2008 17:10'select left(@date,1)select @date, convert(varchar(20), @date)returns:A2008-04-11 17:10:00.000 Apr 11 2008 5:10PMFri, 23 May 2008 09:16:59 GMTYelena VarshalDatetime conversionhttp://www.sqlservercentral.com/Forums/Topic505599-1228-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/SQL+Server+2005/62870/">Datetime conversion</A>[/B]Thu, 22 May 2008 21:06:37 GMTkevriley