Posts Date and Time
Post
Cancel

Date and Time

SQL Tricks - You might consider it a small cheat sheet when working with SQL Date and Time.

source - http://www.dataceptor.com/sql-tricks

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 midnight

origin - http://www.pipiscrew.com/?p=2936 sql-date-and-time

This post is licensed under CC BY 4.0 by the author.
Contents

Trending Tags