Date Formats Used in Automation

Learn more about Toad for SQL Server
at Quest.com

Find solutions and downloads at the
Toad for SQL Server Support Portal

You are here: Automate Tasks > Date Formats Used in Automation

You can customize the format of a date/time suffix used when specifying a file name in an Automation activity. You can also customize the format of a date/time variable that you create in the Set Variable activity. For best results, use the date/time formats described in the following table.

Note: You must surround a date format in a file name suffix with the # sign (for example, #hh-mm-ss#).

Format Specifier	Description and Examples
d	Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero. If the date is August 29, 2008 and time is 7:27:15 pm "d, M" displays 29, 8
dd	Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero. If the date is January 2, 2008 and time is 6:30:15 am "dd, MM" displays 02, 01
h	Represents the hour as a number from 1 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon. A particular hour after midnight is indistinguishable from the same hour after noon. The hour is not rounded, and a single-digit hour is formatted without a leading zero. For example, given a time of 5:43, this custom format specifier displays "5". If the date is January 1, 2008 and time is 6:09:01 pm "h-m-s t" displays 6-9-1 P
hh, hh (plus any number of additional h specifiers)	Represents the hour as a number from 01 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon. A particular hour after midnight is indistinguishable from the same hour after noon. The hour is not rounded, and a single-digit hour is formatted with a leading zero. For example, given a time of 5:43, this format specifier displays "05". If the date is January 1, 2008 and time is 6:09:01 pm "hh-mm-ss tt" displays 06-09-01 PM
H	Represents the hour as a number from 0 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted without a leading zero. If the date is January 1, 2008 and time is 6:09:01 am "HH-mm-ss" displays 6-09-01
HH, HH (plus any number of additional H specifiers)	Represents the hour as a number from 00 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted with a leading zero. The following example includes the HH custom format specifier in a custom format string. If the date is January 1, 2008 and time is 6:09:01 am "HH-mm-ss" displays 6-09-01
m	Represents the minute as a number from 0 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted without a leading zero. If the date is January 1, 2008 and time is 6:09:01 pm "h-m-s t" displays 6-9-1 P
mm, mm (plus any number of additional m specifiers)	Represents the minute as a number from 00 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted with a leading zero. If the date is January 1, 2008 and time is 6:09:01 pm "hh-mm-ss tt" displays 06-09-01 PM
M	Represents the month as a number from 1 through 12. A single-digit month is formatted without a leading zero. If the date is August 8, 2008 "(M) MMM, MMMM" displays (8) Aug, August
MM	Represents the month as a number from 01 through 12. A single-digit month is formatted with a leading zero. If the date is January 2, 2008 "dd, MM" displays 02, 01
s	Represents the seconds as a number from 0 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted without a leading zero. If the date is January 1, 2008 and time is 6:09:01 pm "h-m-s t" displays 6-9-1 P
ss, ss (plus any number of additional s specifiers)	Represents the seconds as a number from 00 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted with a leading zero. The following example includes the ss custom format specifier in a custom format string. If the date is January 1, 2008 and time is 6:09:01 pm "hh-mm-ss tt" displays 06-09-01 PM
t	Represents the first character of the AM/PM designator. If the date is January 1, 2008 and time is 6:09:01 pm "h-m-s t" displays 6-9-1 P
tt, tt (plus any number of additional t specifiers)	Represents the AM/PM designator. If the date is January 1, 2008 and time is 6:09:01 pm "hh-mm-ss tt" displays 06-09-01 PM
y	Represents the year as a one or two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the first digit of a two-digit year begins with a zero (for example, 2008), the number is formatted without a leading zero. If the date is January 1, 2010 "%y" displays 10
yy	Represents the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the two-digit year has fewer than two significant digits, the number is padded with leading zeros to achieve two digits. If the date is January 1, 2010 "yy" displays 10
yyy	Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits. If the date is January 1, 2010 "yyy" displays 2010
yyyy	Represents the year as a four-digit number. If the year has more than four digits, only the four low-order digits appear in the result. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits. If the date is January 1, 2010 "yyyy" displays 2010
:	Represents the time separator. Note: Do not use this character when formatting a date/time suffix for a file name in an Automation activity.
/	Represents the date separator. Note: Do not use this character when formatting a date/time suffix for a file name in an Automation activity.
"	Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks ().
'	Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (') characters.
%c	Represents the result associated with a custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M.

Format Specifier

Description and Examples

Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero.

If the date is August 29, 2008 and time is 7:27:15 pm

"d, M" displays 29, 8

Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero.

If the date is January 2, 2008 and time is 6:30:15 am

"dd, MM" displays 02, 01

Represents the hour as a number from 1 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon. A particular hour after midnight is indistinguishable from the same hour after noon. The hour is not rounded, and a single-digit hour is formatted without a leading zero. For example, given a time of 5:43, this custom format specifier displays "5".

If the date is January 1, 2008 and time is 6:09:01 pm

"h-m-s t" displays 6-9-1 P

hh, hh (plus any number of additional h specifiers)

Represents the hour as a number from 01 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon. A particular hour after midnight is indistinguishable from the same hour after noon. The hour is not rounded, and a single-digit hour is formatted with a leading zero. For example, given a time of 5:43, this format specifier displays "05".

If the date is January 1, 2008 and time is 6:09:01 pm

"hh-mm-ss tt" displays 06-09-01 PM

Represents the hour as a number from 0 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted without a leading zero.

If the date is January 1, 2008 and time is 6:09:01 am

"HH-mm-ss" displays 6-09-01

HH, HH (plus any number of additional H specifiers)

Represents the hour as a number from 00 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted with a leading zero. The following example includes the HH custom format specifier in a custom format string.

If the date is January 1, 2008 and time is 6:09:01 am

"HH-mm-ss" displays 6-09-01

Represents the minute as a number from 0 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted without a leading zero.

If the date is January 1, 2008 and time is 6:09:01 pm

"h-m-s t" displays 6-9-1 P

mm, mm (plus any number of additional m specifiers)

Represents the minute as a number from 00 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted with a leading zero.

If the date is January 1, 2008 and time is 6:09:01 pm

"hh-mm-ss tt" displays 06-09-01 PM

Represents the month as a number from 1 through 12. A single-digit month is formatted without a leading zero.

If the date is August 8, 2008

"(M) MMM, MMMM" displays (8) Aug, August

Represents the month as a number from 01 through 12. A single-digit month is formatted with a leading zero.

If the date is January 2, 2008

"dd, MM" displays 02, 01

Represents the seconds as a number from 0 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted without a leading zero.

If the date is January 1, 2008 and time is 6:09:01 pm

"h-m-s t" displays 6-9-1 P

ss, ss (plus any number of additional s specifiers)

Represents the seconds as a number from 00 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted with a leading zero. The following example includes the ss custom format specifier in a custom format string.

If the date is January 1, 2008 and time is 6:09:01 pm

"hh-mm-ss tt" displays 06-09-01 PM

Represents the first character of the AM/PM designator.

If the date is January 1, 2008 and time is 6:09:01 pm

"h-m-s t" displays 6-9-1 P

tt, tt (plus any number of additional t specifiers)

Represents the AM/PM designator.

If the date is January 1, 2008 and time is 6:09:01 pm

"hh-mm-ss tt" displays 06-09-01 PM

Represents the year as a one or two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the first digit of a two-digit year begins with a zero (for example, 2008), the number is formatted without a leading zero.

If the date is January 1, 2010

"%y" displays 10

Represents the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the two-digit year has fewer than two significant digits, the number is padded with leading zeros to achieve two digits.

If the date is January 1, 2010

"yy" displays 10

yyy

Represents the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits.

If the date is January 1, 2010

"yyy" displays 2010

yyyy

Represents the year as a four-digit number. If the year has more than four digits, only the four low-order digits appear in the result. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits.

If the date is January 1, 2010

"yyyy" displays 2010

Represents the time separator.

Note: Do not use this character when formatting a date/time suffix for a file name in an Automation activity.

Represents the date separator.

Note: Do not use this character when formatting a date/time suffix for a file name in an Automation activity.

Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks ().

Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (') characters.

Represents the result associated with a custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M.