SQL ServerData Types

SQL - DataTypes - Dates

Data Types

  • datetime - this was the original datetime data type defined in sql server.
  • datetime2 - this is the latest and greates. It replaces datetime. has 7 decimal places of fractional seconds (100 nano seconds). This is the ANSI SQL Standard.

Formating Dates

When you perform a 

select aDateColumn from aTable

And you direct this output to a text format, the date column will have a funny format. SQL has a Function command that can be used to format this data.

Here are some examples

1
2
select format(getdate(), 'dd/MM/yyyy') 
select format(aDateColumn, 'yyyyMMdd')

Common formatting code include:

  • dd - the date

  • dddd - the day of the week (ex ‘Wednesday’)

  • MM - the month (as a 2 digit number between 1 and 12)

  • MMMM - the printed month (ex ‘March’)

  • yyyy - the year stated as a 4 digit value

  • hh - hour of the day

  • mm - minute of the day

Date Math

Use theDateAdd function to modify a date.

For example to get a date of 6 months ago today use the following function

1
2
SELECT DATEADD(m, -6, convert(date, convert(varchar(6), getdate(),112) + '01'))
    -- 112 is yyyymmdd - but varchar(6) makes it yyyymm

references
– https://stackoverflow.com/questions/28771952/sql-server-get-date-6-months-in-past
– https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16