DATETIME (TSQL Datatype)

Stores a date and time.

Syntax

DATETIME

DATETIME2

Range

0001-01-01 to 9999-12-31

Remarks

For RSQL, DATETIME2 is synonym of DATETIME.

Literal Datetime

There is no syntax for writing a literal datetime.

You just use a literal string, and it will be converted to datetime.

The best formats to write a literal datetime are 'yyyyMMdd HH:mm:ss.fffffffff' and 'yyyy-MM-ddTHH:mm:ss.fffffffff', as they are unambiguous.

Don’t use a string like ‘1989-03-02’ or ‘1989-03-02 09:30:00’ for datetime, because MS SQL Server can parse it as February 3, 1989, if current language is French.
There is no such problem with the datetime2 or date datatypes, though.
And this problem doesn’t arise with RSQL.

Examples

DECLARE @a DATETIME = '19890302 09:30:00';    -- best format
DECLARE @b DATETIME = '1989-03-02T09:30:00';  -- best format (ISO 8601)
DECLARE @c DATETIME = '19890302';             -- time part is 00:00
DECLARE @d DATETIME = '';

PRINT @a;
PRINT @b;
PRINT @c;
PRINT @d;

The result is:

1989-03-02 09:30:00
1989-03-02 09:30:00
1989-03-02 00:00:00
1900-01-01 00:00:00