Timestamp Format Specifiers

TIMESTAMP values are formatted and parsed using Timestamp Format Specifiers. Timestamp format specifiers are strings that use special characters to describe date and/or time elements in a string representation of a timestamp.

Timestamp format specifiers are used in the following circumstances:

When entering a TIMESTAMP constant with the TIMESTAMP keyword. In this case, timestamp format specifiers are used to describe how the string entered should be parsed in order to obtain a TIMESTAMP value, as in the following example:
```
TimeGenerated > TIMESTAMP ( '2004-05-28 10:23:15', 'yyyy-MM-dd hh:mm:ss' )
```
When converting a TIMESTAMP value to a STRING value using the TO_STRING function. In this case, timestamp format specifiers are used to describe how the TIMESTAMP value should be formatted in order to obtain a STRING value, as in the following example:
```
TO_STRING(TimeGenerated, 'yyyy MMM, dd h:m:s')
```
When converting a STRING value to a TIMESTAMP value using the TO_TIMESTAMP function. In this case, timestamp format specifiers are used to describe how the STRING value should be parsed in order to obtain a TIMESTAMP value, as in the following example:
```
TO_TIMESTAMP(Text, 'MMM ddd yyyy')
```
When specifying how an input format should parse TIMESTAMP fields, using the "iTsFormat" parameter. In this case, timestamp format specifiers are used to describe how timestamp values are represented by the selected data source, so that the input format is capable to parse these fields and represent them as values of type TIMESTAMP.
The following example sets a specific value for the "iTsFormat" parameter of the CSV Input Format:
```
C:\>logparser "SELECT MyField FROM file.csv" -i:CSV -iTsFormat:"yyyy-MM-dd"
```
When specifying how an output format should format and display TIMESTAMP fields, using the "oTsFormat" parameter. In this case, timestamp format specifiers are used to describe how TIMESTAMP values should be formatted by the output format, as in the following example using the TSV Output Format:
```
C:\>logparser "SELECT TimeGenerated INTO file.txt FROM System" -i:EVT -o:TSV -oTsFormat:"yyyy-MM-dd"
```

The following table describes the timestamp format specifiers supported by the Log Parser SQL-Like language:

Specifier	Description	Example specifier strings	Example formats
y	year, last digit (when parsing, assumed to be relative to year 2000)	y MM dd	4 05 28
yy	year, last 2 digits (when parsing, assumed to be relative to year 2000)	yy MM dd	04 05 28
yyy	year, last 3 digits (when parsing, assumed to be relative to year 2000)	yyy MM dd	004 05 28
yyyy	year, 4 digits	yyyy MM dd	2004 05 28
M	month, no leading zero	yyyy-M-dd	2004-5-28 2004-12-01
MM	month, leading zero	yyyy-MM-dd	2004-05-28 2004-12-01
MP	month, leading space	yyyy-MP-dd	2004- 5-28 2004-12-01
MX	month, with or without leading zero (when parsing) month, without leading zero (when formatting)	yyyy-MX-dd	2004-05-28 (when parsing) 2004-5-28 2004-12-01
MMM	month, 3-character abbreviation of name (1)	MMM d, yyyy	Dec 1, 2004
MMMM	month, full name (1)	MMMM d, yyyy	December 1, 2004
d	day, no leading zero	yyyy-MM-d	2004-12-1 2004-05-28
dd	day, leading zero	yyyy-MM-dd	2004-12-01 2004-05-28
dp	day, leading space	yyyy-MM-dp	2004-12- 1 2004-05-28
dx	day, with or without leading zero (when parsing) day, without leading zero (when formatting)	yyyy-MM-dx	2004-12-01 (when parsing) 2004-12-1 2004-05-28
ddd	week day, 3-character abbreviation of name (1)	ddd MMMM d, yyyy	Wed December 1, 2004
dddd	week day, full name (1)	dddd MMMM d, yyyy	Wednesday December 1, 2004
h, H	hour, no leading zero	h:mm:ss	3:12:05 21:04:15
hh, HH	hour, leading zero	hh:mm:ss	03:12:05 21:04:15
hp, HP	hour, leading space	hp:mm:ss	3:12:05 21:04:15
hx, HX	hour, with or without leading zero (when parsing) hour, without leading zero (when formatting)	hx:mm:ss	03:12:05 (when parsing) 3:12:05 21:04:15
m	minute, no leading zero	hh:m:ss	21:4:15 03:12:05
mm	minute, leading zero	hh:mm:ss	21:04:15 03:12:05
mp	minute, leading space	hh:mp:ss	21: 4:15 03:12:05
mx	minute, with or without leading zero (when parsing) minute, without leading zero (when formatting)	hh:mx:ss	21:04:15 (when parsing) 21:4:15 3:12:05
s	second, no leading zero	hh:mm:ss	03:12:5 21:04:15
ss	second, leading zero	hh:mm:ss	03:12:05 21:04:15
sp	second, leading space	hh:mm:sp	03:12: 5 21:04:15
sx	second, with or without leading zero (when parsing) second, without leading zero (when formatting)	hh:mm:ss	03:12:05 (when parsing) 03:12:5 21:04:15
l	millisecond, no leading zeroes	hh:mm:ss.l	21:4:15.5 03:12:05.395
ll	millisecond, leading zeroes	hh:mm:ss.ll	21:04:15.005 03:12:05.395
lp	millisecond, leading spaces	hh:mm:ss.lp	21:04:15. 5 03:12:05.395
lx	millisecond, with or without leading zero (when parsing) millisecond, without leading zero (when formatting)	hh:mm:ss.lx	21:04:15.005 (when parsing) 21:04:15.5 3:12:05.395
n	nanosecond, no leading zeroes	hh:mm:ss.ll.n	21:4:15.005.400 03:12:05.395.1900
nn	nanosecond, leading zeroes	hh:mm:ss.ll.nn	21:04:15.005.00000400 03:12:05.395.001900
np	nanosecond, leading spaces	hh:mm:ss.ll.np	21:04:15.005. 400 03:12:05.395. 1900
nx	nanosecond, with or without leading zero (when parsing) nanosecond, without leading zero (when formatting)	hh:mm:ss.ll.nx	21:04:15.005.00000400 (when parsing) 21:04:15.005.400 3:12:05.395.1900
tt	AM/PM notation	hh:mm:ss tt	09:04:15 PM 03:12.05 AM
?	any character (when parsing) space (when formatting)	yyyy-MM-dd?hh:mm:ss	2004-05-28T21:04:15 (when parsing) 2004-05-28 21:04:15 (when formatting)
any other character	verbatim character	hh:mm:ss --- yyyy.MM+dd	09:04:15 --- 2004.05+28

Notes:: (1): element names are obtained from the current system locale.

Date-only and Time-only Timestamps

When parsing a timestamp string, the following assumptions are made:

If the timestamp format specifiers include date elements only, the resulting TIMESTAMP value will be a date-only timestamp; for example, the following statement creates a date-only TIMESTAMP constant value:
```
TIMESTAMP('2004-05-28', 'yyyy-MM-dd')
```
If the timestamp format specifiers include time of day elements only, the resulting TIMESTAMP value will be a time-only timestamp; for example, the following statement creates a time-only TIMESTAMP constant value:
```
TIMESTAMP('21:04:15', 'hh:mm:ss')
```
Unspecified date elements are replaced with the corresponding elements of the Log Parser origin date (January 1, year 0), unless the timestamp is a time-only timestamp value; for example, the following statement creates a date-only timestamp representing the date February 1, year 0:
```
TIMESTAMP('2', 'M')
```
Similarly, unspecified time elements are replaced with zero values, unless the timestamp is a date-only timestamp value; for example, the following statement creates a time-only timestamp representing the time 10:00:00.0.0:
```
TIMESTAMP('10', 'h')
```
As another example, the following statement creates a full timestamp value representing the time 10:00:00.0.0 on February 1, year 0:
```
TIMESTAMP('2 10', 'M h')
```

For more information on date-only and time-only timestamp values, refer to the Timestamp Data Type reference.

Timestamp Format Specifiers

Date-only and Time-only Timestamps

See also: