Programming, SQL Database, Tips & Tricks

DateTime conversion and Snippets in MSSQL

Below you can find snippets for SQL to get data for different parts of date and time and all below queries tested in MS SQL server.

DateTime Conversions

MSSQL string to datetime conversion

-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112)
-- ISO yyyymmdd
-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113)
-- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114)
-- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120)
-- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121)
-- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard
-- works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126)
-- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513

-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000

DateTime Snippets

Year

First day of this year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

First day of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0)

Last day of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)

Last day of this year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1)

First day of next year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)

Last day of next year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, -1)

Months

First day of next month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Last day of next month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, -1)

First day of previous month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)

Last Day of previous month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)

First day of this month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Last day of this month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)

Quarters

First day of previous quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) -1, 0)

Last day of previous quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), -1)

First day of this quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)

Last day of this quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, -1)

First day of next quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0)

Last day of next quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 2, -1)

First day of 1st quarter of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)

Last day of 1st quarter of previous year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1))

First day of 2nd quarter of previous year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

Last day of 2nd quarter of previous year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1))

First day of 3rd quarter of previous year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

Last day of 3rd quarter of previous year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1))

First day of 4th quarter of previous year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

Last day of 4th quarter of previous year

SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1))

First day of 1st quarter of current year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

Last day of 1st quarter of current year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))

First day of 2nd quarter of current year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

Last day of 2nd quarter of current year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))

First day of 3rd quarter of current year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

Last day of 3rd quarter of current year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))

First day of 4th quarter of current year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

Last day of 4th quarter of current year

SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))

Half Years

First day of second half of this year

SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

First day of second half of next year

SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0))

First day of second half of previous year

SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0))

Other general use

Now

SELECT GETDATE()

Yesterday

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)

Today

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

Tomorrow

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)

Day of month

SELECT DAY(GETDATE())

30 days ago

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -30)

90 days ago

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -90)

1 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, DAY(GETDATE())-1)

3 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, DAY(GETDATE())-1)

6 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, DAY(GETDATE())-1)

12 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, DAY(GETDATE())-1)

Leave a Reply

Your email address will not be published. Required fields are marked *