Date Data Type in SQL Server

Date is the SQL Server data type that we use to store Date. It stores the Date without time & without time zone information.

How SQL Server stores date

The SQL Server stores both date & time as integers. The date integer stores the no of days elapsed since a base date (which is 1900-01-01).

Since Date is internally a integer, we can convert any integer to date using the cast function. Convert 0 to date will give you the base date.

Similarly, you can convert the current date to int. The GETDATE() method returns the current date

Date in SQL Server

Use Date Data type to create a Date column/variable without any time information. The time defaults to 00:00:00 and sql server will not store it in the Database. Hence it is very efficient way to store date, when there is no need to store time.

The Syntax for creating a table column with date is as follows

Inserting Date Values

Storage size & Range

Min Date0001-01-01
Max Date9999-12-31
Storage3 Bytes
Default Value1900-01-01
Default string literal formatYYYY-MM-DD

Displaying the Date

The date is stored as number and is independent of any formats.

SQL Server by default uses the YYYY-MM-DD to display the date. You can customize that using the format function. The article on SQL Server date formats lists the various date formats and how to convert them.

You can change the default language in SQL Server, which also changes the display format of the date.

Format for Inserting the date

While you can display the date in any formats, you need to be careful while inserting the date in the database.

This is because, the date 02/03/2020 can be interpreted as 2nd march or 3rd February depending on where you live.

YYYYMMDD Universal format

Always use the format YYYYMMDD to insert the date into database. This is the default format that SQL Server uses. It is also the safe format and can be interpreted only in one way.

The following query will run without error irrespective of the language or DATEFORMAT used by the SQL Server

Language Settings

The language settings also determines the date formats, that you can use to insert the date values.

To find out the current language, run the TSQL command and note down the language.

Next, run the exec sp_helplanguage to procedure. Look for the value of dateformat column.

The format may one of dmy, mdy & ymd.

IF format is dmy, then you can insert the value 12-15-2020. But if the format is mdy it will result in an error.

Using DATEFORMAT

You can also use the DATEFORMAT to set the format to dmy, mdy & ymd. The setting will work only for the current session. When a new session starts the DATEFORMAT revert back to the default.

Two digit year

The year part can be specified either as four digit or two-digit years.  Use four-digit years whenever possible.

For two digits year, the cut of year is 2049 and the time span is from 1950 to 2049.

Hence the years from 50 to 99 is treated as 1950 to 1999. And the year from 00 to 49 is treated as 2000 to 2049.

You can change the cutoff year using the two digit year cutoff option

Separators

You can use the dash marks (/), hyphens (-), or periods (.) as separators.

Reference

date (Transact-SQL)

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top