SQL Server Date Time Format

In SQL Server used Cast or Convert function to Format DateTime value or column into a specific date format.Both function are used to convert datetime to varchar or string.
CAST function Syntax: CAST(expression as data_type)
Let's convert current date time to varchar
select cast(getdate() as varchar)
CONVERT function is used to change or convert the DateTime formats.By using convert function you
can get only Date part or only Time part from the datetime.
CONVERT Function Syntax: CONVERT(data_type,expression,date Format style)
Let's take Sql Server DateTtime styles example:
Format Query
USA mm/dd/yy select convert(varchar, getdate(), 1)
ANSI yy.mm.dd select convert(varchar, getdate(), 2)
British/French dd/mm/yy select convert(varchar, getdate(), 3)
German dd.mm.yy select convert(varchar, getdate(), 4)
Italian dd-mm-yy select convert(varchar, getdate(), 5)
dd mon yy select convert(varchar, getdate(), 6)
Mon dd, yy select convert(varchar, getdate(), 7)
USA mm-dd-yy select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd select convert(varchar, getdate(), 11)
ISO yymmdd select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM) select convert(varchar, getdate(), 100)
mm/dd/yyyy select convert(varchar, getdate(), 101)
yyyy.mm.dd select convert(varchar, getdate(), 102)
dd/mm/yyyy select convert(varchar, getdate(), 103)
dd.mm.yyyy select convert(varchar, getdate(), 104)
dd-mm-yyyy select convert(varchar, getdate(), 105)
dd mon yyyy select convert(varchar, getdate(), 106)
Mon dd, yyyy select convert(varchar, getdate(), 107)
hh:mm:ss select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) select convert(varchar, getdate(), 109)
mm-dd-yyyy select convert(varchar, getdate(), 110)
yyyy/mm/dd select convert(varchar, getdate(), 111)
yyyymmdd select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h) select convert(varchar, getdate(), 114)

--select replace(convert(varchar(11), getdate(), 113),' ','-')
--SELECT CONVERT(VARCHAR(12), CAST(getdate() AS datetime), 103)
--SELECT CONVERT(VARCHAR(12), GETDATE(), 101)
--SELECT CONVERT(VARCHAR(12), CAST('5/3/2001' AS SMALLDATETIME), 104)
--select cast( getDate() as datetime )
--select  CAST(CAST(getdate() As VARCHAR(12)) AS DATETIME)
--SELECT CONVERT( varchar, GETDATE() , 101)
--select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
--select cast(convert(char(11), getdate(), 113) as datetime)
--SET LANGUAGE 'English'
--PRINT CONVERT(DATETIME,'2009-12-24')
--SET LANGUAGE 'Spanish'
--PRINT CONVERT(DATETIME,'2009-12-24')



CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Where data_type will be varchar or char, length will be the total length of your expected format, expression will be any valid sql server expression & style will denote the output format.

The list of available styles are given below:
Style Format Example
1 MM/dd/yy 04/25/09
2 yy.MM.dd 09.04.25
3 dd/MM/yy 25/04/09
4 dd.MM.yy 25.04.09
5 dd-MM-yy 25-04-09
6 dd MMM yy 25 Apr 09
7 MMM dd, yy Apr 25, 09
10 MM-dd-yy 04-25-09
11 yy/MM/dd 09/04/25
12 yyMMdd 090425
100 or 0 MMM dd yyyy hh:miAM (or PM) Apr 25 2009 1:10PM
101 MM/dd/yyyy 04/25/2009
102 yyyy.MM.dd 2009.04.25
103 dd/MM/yyyy 25/04/2009
104 dd.MM.yyyy 25.04.2009
105 dd-MM-yyyy 25-04-2009
106 dd MMM yyyy 25 Apr 2009
107 MMM dd, yyyy Apr 25, 2009
108 hh:mm:ss 13:12:22
109 or 9 MMM dd yyyy hh:mi:ss:mmmAM (or PM) Apr 25 2009 1:12:40:263PM
110 MM-dd-yyyy 04-25-2009
111 yyyy/MM/dd 2009/04/25
112 yyyyMMdd 20090425
113 or 13 dd MMM yyyy hh:mm:ss:mmm(24h) 25 Apr 2009 13:13:30:983
114 hh:mi:ss:mmm(24h) 13:13:42:200
Ok now you know the style value and its corresponding format. Now i want to show you how we can use this style or format in sql server query statement. You can also use this format from asp.net C# application when you built a query string. You can also use those formats in your where clause as conditional purposes.
Query Output
SELECT CONVERT(VARCHAR, getdate(), 1) 04/25/09
SELECT CONVERT(VARCHAR, getdate(), 10) 04-25-09
SELECT CONVERT(VARCHAR, getdate(), 100) Apr 25 2009 1:25PM
SELECT CONVERT(VARCHAR, getdate(), 114) 13:25:48:153

The above all formats may does not satisfy your condition. So you have to create a custom statement like my first example.



//================================================//

• Homepage
• All examples
• Contact me


String Format for DateTime [C#]
This example shows how to format DateTime using String.Format method. All formatting can be done also using DateTime.ToString method.
Custom DateTime Formatting
There are following custom format specifiers y (year), M (month), d (day), h (hour 12), H (hour 24), m (minute), s (second), f (second fraction), F (second fraction, trailing zeroes are trimmed),t (P.M or A.M) and z (time zone).
Following examples demonstrate how are the format specifiers rewritten to the output.
[C#]

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone

You can use also date separator / (slash) and time sepatator : (colon). These characters will be rewritten to characters defined in the current DateTimeForma-tInfo.DateSepa¬rator andDateTimeForma¬tInfo.TimeSepa¬rator.
[C#]

// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)

Here are some examples of custom date and time formatting:
[C#]

// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt);            // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt);    // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt);  // "Sunday, March 9, 2008"

// two/four digit year
String.Format("{0:MM/dd/yy}", dt);            // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

Standard DateTime Formatting
In DateTimeForma¬tInfo there are defined standard patterns for the current culture. For example property ShortTimePattern is string that contains value h:mm tt for en-US culture and value HH:mm for de-DE culture.
Following table shows patterns defined in DateTimeForma¬tInfo and their values for en-US culture. First column contains format specifiers for the String.Format method.
Specifier DateTimeFormatInfo property Pattern value (for en-US culture)
t ShortTimePattern h:mm tt
d ShortDatePattern M/d/yyyy
T LongTimePattern h:mm:ss tt
D LongDatePattern dddd, MMMM dd, yyyy
f (combination of D and t) dddd, MMMM dd, yyyy h:mm tt
F FullDateTimePattern dddd, MMMM dd, yyyy h:mm:ss tt
g (combination of d and t) M/d/yyyy h:mm tt
G (combination of d and T) M/d/yyyy h:mm:ss tt
m, M MonthDayPattern MMMM dd
y, Y YearMonthPattern MMMM, yyyy
r, R RFC1123Pattern ddd, dd MMM yyyy HH':'mm':'ss 'GMT' (*)
s SortableDateTi¬mePattern yyyy'-'MM'-'dd'T'HH':'mm':'ss (*)
u UniversalSorta¬bleDateTimePat-tern yyyy'-'MM'-'dd HH':'mm':'ss'Z' (*)
  (*) = culture independent
Following examples show usage of standard format specifiers in String.Format method and the resulting output.
[C#]

String.Format("{0:t}", dt);  // "4:05 PM"                         ShortTime
String.Format("{0:d}", dt);  // "3/9/2008"                        ShortDate
String.Format("{0:T}", dt);  // "4:05:07 PM"                      LongTime
String.Format("{0:D}", dt);  // "Sunday, March 09, 2008"          LongDate
String.Format("{0:f}", dt);  // "Sunday, March 09, 2008 4:05 PM"  LongDate+ShortTime
String.Format("{0:F}", dt);  // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt);  // "3/9/2008 4:05 PM"                ShortDate+ShortTime
String.Format("{0:G}", dt);  // "3/9/2008 4:05:07 PM"             ShortDate+LongTime
String.Format("{0:m}", dt);  // "March 09"                        MonthDay
String.Format("{0:y}", dt);  // "March, 2008"                     YearMonth
String.Format("{0:r}", dt);  // "Sun, 09 Mar 2008 16:05:07 GMT"   RFC1123
String.Format("{0:s}", dt);  // "2008-03-09T16:05:07"             SortableDateTime
String.Format("{0:u}", dt);  // "2008-03-09 16:05:07Z"            UniversalSortableDateTime


See also
• [C#] String Format for Double – format float numbers
• [C#] String Format for Int – format (align) integer numbers
• [C#] IFormatProvider for Numbers – parse float numbers with IFormatProvider
• [C#] Custom IFormatProvider – string formatting with custom IFormatProvider
• [C#] Align String with Spaces – how to align text to the right or left
• [C#] Indent String with Spaces – how to indent text with repeated spaces
• Custom Date and Time Format Strings – MSDN – custom date-time formatting
• Standard Date and Time Format Strings – MSDN – standard date-time formatting
• DateTimeForma¬tInfo – MSDN – date-time patterns for specific cultures
• String.Format – MSDN – method to format strings
By Jan Slama, 25-Mar-2008

Copyright © 2008 Jan Slama.