Date and time

Querona supports the SQL Server date and time types — date, datetime2, datetime, smalldatetime, time and the time-zone-aware datetimeoffset — which together cover the date and time needs of most applications.

date

Defines a date in Querona.

Syntax

date

Description

Property	Description
Range	0001-01-01 through 9999-12-31
Accuracy	1 day

Example

SELECT CAST('2017-05-04' AS date) AS 'date';

datetime2

Defines a date combined with a time of day, based on a 24-hour clock. datetime2 is an extension of datetime with a larger date range, a larger default fractional precision, and optional user-specified precision.

Syntax

datetime2 [ (fractional seconds precision) ]

Description

Property	Description
Date range	0001-01-01 through 9999-12-31
Time range	00:00:00 through 23:59:59.9999999
Accuracy	100 nanoseconds

Example

SELECT CAST('2017-05-04 15:55:44.1234567' AS datetime2(7)) AS 'datetime2';

datetime

Defines a date combined with a time of day with fractional seconds, based on a 24-hour clock.

Syntax

datetime

Description

Property	Description
Date range	1753-01-01 through 9999-12-31
Time range	00:00:00 through 23:59:59.997
Accuracy	Rounded to increments of .000, .003, or .007 seconds

Example

SELECT CAST('2017-05-04 15:55:44.132' AS datetime) AS 'datetime';

smalldatetime

Defines a date combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Syntax

smalldatetime

Description

Property	Description
Date range	1900-01-01 through 2079-06-06
Time range	00:00:00 through 23:59:59
Accuracy	One minute

Example

SELECT CAST('2017-05-04 15:55:44.132' AS smalldatetime) AS 'smalldatetime';

time

Defines a time of day. The time is without time-zone awareness and is based on a 24-hour clock.

Syntax

time [ (fractional second precision) ]

Description

Property	Description
Range	00:00:00.0000000 through 23:59:59.9999999
Accuracy	100 nanoseconds

Example

SELECT CAST('2017-05-04 15:55:44.132' AS time(7)) AS 'time';

datetimeoffset

Defines a date combined with a time of day and a time-zone offset from UTC, based on a 24-hour clock. datetimeoffset has the date and time range of datetime2 and optional user-specified fractional seconds precision. Two values are equal when they denote the same instant, whatever their offsets.

Syntax

datetimeoffset [ (fractional seconds precision) ]

Description

Property	Description
Date range	0001-01-01 through 9999-12-31
Time range	00:00:00 through 23:59:59.9999999
Time-zone offset range	-14:00 through +14:00
Accuracy	100 nanoseconds

Example

SELECT CAST('2017-05-04 15:55:44.1234567 +02:00' AS datetimeoffset(7)) AS 'datetimeoffset',
       SWITCHOFFSET(CAST('2017-05-04 15:55:44 +02:00' AS datetimeoffset(0)), '-05:00') AS 'switched';

Fractional seconds precision

The fractional seconds precision of time, datetime2 and datetimeoffset ranges from 0 to 7 and defaults to 7. A larger precision anywhere a type is written, such as CAST, CONVERT, DECLARE or a column definition, fails with error 1002, and the whole batch is refused before any of its statements runs.

Conversion to a lower precision

When a value is converted to a type or a fractional seconds precision that holds fewer digits, Querona rounds it the way SQL Server does. This applies to CAST and CONVERT, to assignment to a variable, to INSERT into a column, and to the result of DATEADD, which keeps the precision of its date argument.

• datetime2(n), datetimeoffset(n) and time(n) round half up to n digits. datetime rounds to .000, .003 or .007 seconds, and smalldatetime rounds to the minute, with 30 seconds rounding up.

• A value that rounds up carries into the next second, minute, day and year.

• A value that would round past the largest value of datetime2, datetimeoffset or time is truncated instead. Converting 9999-12-31 23:59:59.9999999 to datetime2(0) gives 9999-12-31 23:59:59.

• Converting to time never moves past midnight: 23:59:59.9999999 converted to time(0) gives 23:59:59. DATEADD on a time value does wrap around midnight.

• A datetime or smalldatetime result outside the range of its type fails with error 242, and a DATEADD result outside the range of its type with error 517. The range is checked after rounding, so 2079-06-06 23:59:30 converted to smalldatetime fails.

A datetime value is stored in three-hundredths of a second, so converting 12:34:56.997 to datetime2(7) gives 12:34:56.9966667, and DATEPART(millisecond, ...) on it returns 996.

Converting a time value to a character string writes as many fraction digits as its precision: time(3) gives 12:34:56.100. CONVERT accepts the time styles 0, 8, 9, 13, 14, 20 to 22, 24 to 27, 100, 108, 109, 113, 114, 120, 121, 126, 127, 130 and 131.

Comparing two values of different precision does not round either of them. The value with fewer digits is compared at the higher precision.

DECLARE @value datetime2(7) = '2026-07-07 12:34:56.1234567';

SELECT CAST(@value AS datetime2(0)) AS 'seconds',   -- 2026-07-07 12:34:56
       CAST(@value AS datetime2(4)) AS 'scale 4',   -- 2026-07-07 12:34:56.1235
       CAST(@value AS time(0))      AS 'time';      -- 12:34:56

Conversion from character strings

Querona reads a character string as a date and time value the way SQL Server does, under the session SET DATEFORMAT and SET LANGUAGE settings.

• datetime and smalldatetime follow DATEFORMAT for numeric dates, including ydm. date, datetime2, datetimeoffset and time read a four-digit year first as year-month-day whatever DATEFORMAT says. Under ydm they refuse a numeric date with a two-digit year with error 9808.

• Month names are recognized in the session language, in full and abbreviated form. March, 1999 gives the first day of the month.

• time reads the same strings as datetime2 and keeps the time of day. A fraction that would round up to midnight is truncated instead: 23:59:59.9999999 converted to time(3) gives 23:59:59.999.

• A string that is not a date and time fails with error 241 (295 for smalldatetime). A valid form with a value outside the range of the type, such as February 30 read as datetime, fails with error 242, or gives NULL when both ANSI_WARNINGS and ARITHABORT are off. A string that is not a time always fails.

Conversion to character strings

CAST and CONVERT of a datetime2, datetimeoffset or time value write as many fraction digits as the precision of the value, in every style. Styles 126 and 127 leave out a zero fraction, and style 127 writes a datetimeoffset value in UTC with a Z suffix. Styles 130 and 131 use the Hijri calendar and fail with error 9814 for a date before 622-07-18. Month names are always English. A target string shorter than the result is truncated without an error.