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:

y

yy

yyy

yyyy

M

MM

MP

MX

MMM

MMMM

d

dd

dp

dx

ddd

dddd

h, H

hh, HH

hp, HP

hx, HX

m

mm

mp

mx

s

ss

sp

sx

l

ll

lp

lx

n

nn

np

nx

tt

?

any other
character

Notes:

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

Date-only and Time-only Timestamps

• 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')
  

See also:

© 2004 Microsoft Corporation. All rights reserved.