Date, time, and timestamp format

Describes string format used to upsert data into columns.

To upsert a value into date, time, or timestamp columns, use the string format or the date or conversion functions (TODATE and TOTIMESTAMP) with the NOW function.

Tip: Timestamp also supports upserting the value as an integer.
Use the formats in INSERT and UPDATE statements.
date
date string format:
yyyy-mm-dd
Where the elements of a date are separated with a hyphen:
  • yyyy: four digit year
  • mm: two digit month
  • dd: two digit day
For example, May 5, 2017:
2017-05-05
time
time string format:
HH:MM:SS[.fff]
where
  • HH: two digit hour using a 24 hour clock.
  • MM: two digit minute.
  • SS: two digit seconds.
  • (Optional) .fff: up to three digit sub-seconds; when excluded it's set to zero (0).
For example, one o'clock in the afternoon
13:00:00.000
13:00:00
timestamp
timestamp combines both date and time with the addition of time zone in ISO 8601 format:
yyyy-mm-dd[(T| )HH:MM:SS[.fff]][(+|-)NNNN]
Where only the date portion of the timestamp is required:
  • (Required) date (yyyy-mm-dd), where
    • yyyy: four digit year
    • mm: two digit month
    • dd: two digit day
  • time (HH:MM:SS[.fff], where
    • HH: two digit hour using a 24 hour clock.
    • MM: two digit minute.
    • SS: two digit seconds.
    • (Optional) .fff: up to three digit sub-seconds; when excluded it's set to zero (0).
    Note: When time is excluded, it's set to zero.
  • timezone ((+|-)NNNN): is the offset from GMT.
    • +|- indicates whether to add or subtract the NNNN from GMT
    • NNNN is the RFC-822 4-digit time zone, for example +0000 is GMT and -0800 is PST.
    Note: When timezone is excluded, it's set to the client or coordinator timezone.
For example, May 5, 2017 midnight GMT:
2017-05-05 00:00:00.000+0000
2017-05-05 00:00:00.000
2017-05-05 00:00:00
2017-05-05