A list of time related SQL scripts

timeseries-scripts.sql

--Date and Time
--These statements are tested with T-SQL under MS SQL Server.
---Months

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) -- First day of previous month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), - 1) -- Last Day of previous month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- First day of this month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, - 1) -- Last day of this month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) -- First day of next month

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, - 1) -- Last day of next month
----	Quarters

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) - 1, 0) -- First day of previous quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), - 1) -- Last day of previous quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) -- First day of this quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, - 1) -- Last day of this quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0) -- First day of next quarter

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 2, - 1) -- Last day of next quarter

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) -- First day of 1st quarter of previous year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, - 1)) -- Last day of 1st quarter of previous year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 2nd quarter of previous year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, - 1)) -- Last day of 2nd quarter of previous year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 3rd quarter of previous year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, - 1)) -- Last day of 3rd quarter of previous year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 4th quarter of previous year

SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, - 1)) -- Last day of 4th quarter of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- First day of 1st quarter of current year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) -- Last day of 1st quarter of current year

SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 2nd quarter of current year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) -- Last day of 2nd quarter of current year

SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 3rd quarter of current year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) -- Last day of 3rd quarter of current year

SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 4th quarter of current year

SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1)) -- Last day of 4th quarter of current year
----	Years

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) -- First day of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), - 1) -- Last day of previous year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- First day of this year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, - 1) -- Last day of this year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) -- First day of next year

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, - 1) -- Last day of next year
----	Half Years

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()), 0)) -- First day of second half of this year

SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) -- First day of second half of next year
----	Other

SELECT GETDATE() -- Now

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

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

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

SELECT DAY(GETDATE()) -- Day of month

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

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

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, DAY(GETDATE()) - 1) -- 1 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, DAY(GETDATE()) - 1) -- 3 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, DAY(GETDATE()) - 1) -- 6 months ago since last midnight

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, DAY(GETDATE()) - 1) -- 12 months ago since last mid