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/yyyyconvert(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
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
ReplyDeletebrillant 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