Wednesday, 15 February 2012

CONVERT CAST dates in T-SQL

Sometimes you need to compare dates as text rather than in date format, and this can be especially useful when comparing dates from different systems using different data types. But T-SQL makes it easy to format different dates using the CONVERT function.

For example, if you have a date stored as text in the dd/mm/yyyy format, then you can convert a standard datetime format from yyyy-mm-dd hh:mm:ss.sss using the native style formats.
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SELECT convert(varchar, getdate(), 100)- mon dd yyyy hh:mmAM
CONVERT(varchar,CloseDate__c,100) AS FormattedCloseDate
Other formats include:
convert(varchar, getdate(), 101) - mm/dd/yyyy
convert(varchar, getdate(), 102) – yyyy.mm.dd
convert(varchar, getdate(), 103) – dd/mm/yyyy
convert(varchar, getdate(), 104) – dd.mm.yyyy
convert(varchar, getdate(), 105) – dd-mm-yyyy
convert(varchar, getdate(), 106) – dd mon yyyy
convert(varchar, getdate(), 107) – mon dd, yyyy
convert(varchar, getdate(), 108) – hh:mm:ss
convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM
convert(varchar, getdate(), 110) – mm-dd-yyyy
convert(varchar, getdate(), 111) – yyyy/mm/dd
convert(varchar, getdate(), 112) – yyyymmdd
convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

See also http://www.w3schools.com/sql/func_convert.asp
and http://msdn.microsoft.com/en-us/library/ms187928.aspx

2 comments:

  1. nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanx a ton once again, Regards, Difference Between sql and tsql

    ReplyDelete
  2. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, Difference sql and tsql


    ReplyDelete