|
Date Format strings as defined by Microsoft are very complex, and not for the faint hearted. However they can be important when working with strings that represent dates.
Standard Date Specifiers
IMPORTANT: Date formatting is heavily dependant on the system's regional settings. The example strings below are from the en-GB locale.
Specifier
|
Type
|
Example (using the current Date and Time)
|
d
|
Short date
|
10/12/2002
|
D
|
Long date
|
December 10, 2002
|
t
|
Short time
|
10:11 PM
|
T
|
Long time
|
10:11:29 PM
|
f
|
Full date & time
|
December 10, 2002 10:11 PM
|
F
|
Full date & time (long)
|
December 10, 2002 10:11:29 PM
|
g
|
Default date & time
|
10/12/2002 10:11 PM
|
G
|
Default date & time (long)
|
10/12/2002 10:11:29 PM
|
M
|
Month day pattern
|
December 10
|
r
|
RFC1123 standard date string
|
Tue, 10 Dec 2002 22:11:29 GMT
|
s
|
Sortable date string
|
2002-12-10T22:11:29
|
u
|
Universal sortable, local time
|
2002-12-10 22:13:50Z
|
U
|
Universal sortable, GMT
|
December 11, 2002 3:13:50 AM
Note: Although named 'sortable', this format cannot actually be sorted or at least not in anything we would recognise as date order
|
Y
|
Year month pattern
|
December, 2002
|
Custom Date Specifiers
If none of the above formats work for you, you can generate your own format strings to fit your needs by combining the following components into a custom format string:
Specifier
|
Type
|
Example
|
Example Output
|
dd
|
Day
|
"dd"
|
10
|
ddd
|
Day name
|
"ddd"
|
Tue
|
dddd
|
Full day name
|
"dddd"
|
Tuesday
|
f, ff, ...
|
Second fractions
|
"fff"
|
932
|
gg, ...
|
Era
|
"gg"
|
A.D.
|
hh
|
2 digit hour
|
!hh"
|
10
|
HH
|
2 digit hour, 24hr format
|
"HH"
|
22
|
mm
|
Minute, 00-59
|
"mm"
|
38
|
MM
|
Month number two digits with leading zero as needed 01-12
|
"MM"
|
12
|
MMM
|
Month abbreviation
|
"MMM"
|
Dec
|
MMMM
|
Full month name
|
:"MMMM"
|
December
|
ss
|
Seconds with leading zeroes as needed 00-59
|
"ss"
|
46
|
tt
|
AM or PM
|
"tt"
|
PM
|
yy
|
2 digit Year
|
"yy"
|
02
|
yyyy
|
Year
|
"yyyy"
|
2002
|
zz
|
2 digit Time Zone offset
|
"zz"
|
-05:00
|
:
|
Time Part Separator
|
"0:hh:mm:ss"
|
10:43:20
|
/
|
Date Part Separator
|
"dd/MM/yyyy"
|
10/12/2002
|
Examples
You can combine these options to match the format to generate a date and time value from the script based on the date 10th December 2002 at 10:43:46:
SQL Date
"yyyy-MM-dd HH:mm:ss"
matches 2002-12-10 10:43:20
US Short Date
"MM/dd/yyyy"
matches 12/10/2002
UK Short Date
"dd/MM/yyyy"
matches 12/10/2002
See Also: SetFileDate
|