Programming, SQL Database, Tips & Tricks

DateTime conversion and Snippets in MSSQL

http://fbmedical.fr/aftepaes/7071 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

click MSSQL string to datetime conversion

follow url -- 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

get link First day of this year

go to site SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)

http://www.cmtagency.com/?mikro=rencontre-femme-jolie-femmes-c%C3%A9libataires&7f5=63 First day of previous year

bekanntschaften mit bild SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0)

enter site Last day of previous year

norges beste datingside SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)

go to site 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 *