Datetime Formatting .NET & SQL

Another problem, another solution.

Date time formatting in .NET and SQL cannot be more easy but people simply do not know about it and you have to knock your heads in a hundred places to find what you are looking for. This article plans to summarize on the same.

ASP.NET

Dim dt As DateTime = DateTime.Now
Dim strDate As String = ""
strDate = dt.ToString("MM/dd/yyyy") ' 07/21/2007
strDate = dt.ToString("dddd, dd MMMM yyyy") 'Saturday, 21 July 2007
strDate = dt.ToString("dddd, dd MMMM yyyy HH:mm") ' Saturday, 21 July 2007 14:58
strDate = dt.ToString("dddd, dd MMMM yyyy hh:mm tt") ' Saturday, 21 July 2007 03:00 PM
strDate = dt.ToString("dddd, dd MMMM yyyy H:mm") ' Saturday, 21 July 2007 5:01
strDate = dt.ToString("dddd, dd MMMM yyyy h:mm tt") ' Saturday, 21 July 2007 3:03 PM
strDate = dt.ToString("dddd, dd MMMM yyyy HH:mm:ss") ' Saturday, 21 July 2007 15:04:10
strDate = dt.ToString("MM/dd/yyyy HH:mm") ' 07/21/2007 15:05
strDate = dt.ToString("MM/dd/yyyy hh:mm tt") ' 07/21/2007 03:06 PM
strDate = dt.ToString("MM/dd/yyyy H:mm") ' 07/21/2007 15:07
strDate = dt.ToString("MM/dd/yyyy h:mm tt") ' 07/21/2007 3:07 PM
strDate = dt.ToString("MM/dd/yyyy HH:mm:ss") ' 07/21/2007 15:09:29
strDate = dt.ToString("MMMM dd") ' July 21
strDate = dt.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK") ' 2007-07-21T15:11:19.1250000+05:30
strDate = dt.ToString("ddd, dd MMM yyyy HH':'mm':'ss 'GMT'") ' Sat, 21 Jul 2007 15:12:16 GMT
strDate = dt.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss") ' 2007-07-21T15:12:57
strDate = dt.ToString("HH:mm") ' 15:14
strDate = dt.ToString("hh:mm tt") ' 03:14 PM
strDate = dt.ToString("H:mm") ' 5:15
strDate = dt.ToString("h:mm tt") ' 3:16 PM
strDate = dt.ToString("HH:mm:ss") ' 15:16:29
strDate = dt.ToString("yyyy'-'MM'-'dd HH':'mm':'ss'Z'") ' 2007-07-21 15:17:20Z
strDate = dt.ToString("dddd, dd MMMM yyyy HH:mm:ss") ' Saturday, 21 July 2007 15:17:58
strDate = dt.ToString("yyyy MMMM") ' 2007 July


SQL

select convert(char, getdate(), 100) --mon dd yyyy hh:mmAM (or PM)
select convert(char, getdate(), 101) --mm/dd/yyyy
select convert(char, getdate(), 102) --yyyy.mm.dd
select convert(char, getdate(), 103) --dd/mm/yyyy
select convert(char, getdate(), 104) --dd.mm.yyyy
select convert(char, getdate(), 105) --dd-mm-yyyy
select convert(char, getdate(), 106) --dd mon yyyy
select convert(char, getdate(), 107) --mon dd, yyyy
select convert(char, getdate(), 108) --hh:mm:ss
select convert(char, getdate(), 109) --mon dd yyyy hh:mm:ss:mmmAM (or PM)
select convert(char, getdate(), 110) --mm-dd-yyyy
select convert(char, getdate(), 111) --yyyy/mm/dd
select convert(char, getdate(), 112) --yyyymmdd
select convert(char, getdate(), 113) --dd mon yyyy hh:mm:ss:mmm
select convert(char, getdate(), 114) --hh:mm:ss:mmm(24h)
select convert(char, getdate(), 120) --yyyy-mm-dd hh:mm:ss(24h)
select convert(char, getdate(), 121) --yyyy-mm-dd hh:mm:ss.mmm
SUBSTRING(RIGHT(CONVERT( VARCHAR, dateassigned, 100),7),1,5) + ' ' + SUBSTRING(RIGHT(CONVERT( VARCHAR, dateassigned, 100),7),6,7) hh:mm AM


Hope this saved you some time.