SQL ServerData Types

SQL - DataTypes - Dates

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