sql - date queries

--Get month names with month numbers

;WITH months(MonthNumber) AS

(

    SELECT 0

    UNION ALL

    SELECT MonthNumber+1

    FROM months

    WHERE MonthNumber < 12

)

SELECT DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS [MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS MonthNumber

FROM months

ORDER BY Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE()))

;

--Get name of day

select DATENAME(WEEKDAY, GETDATE()) AS TodayIs

--Get first date of current month

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate;

--Get last date of current month

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) LastDate;

--Get first date of previous month

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) FirstDayOfPreviousMonth

--Get last date of previous month

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) LastDayOfPreviousMonth

--Get first date of current year

SELECT dateadd(year,datediff(year,0,getdate()),0) FirstDateOfYear

--Get last date of current year

SELECT dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))) LasteDateOfYear

--Calculate age from birth date

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int

SELECT @date = '05/05/1989'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END

SELECT @tmpdate = DATEADD(m, @months, @tmpdate)

SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years AS Years, @months AS Months, @days AS Days

--Get days between two dates

SELECT DATEDIFF(DAY, '1/1/2016', '3/1/2016') DaysDifference

--source: http://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedinhttp://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know#.Ws2ZzyibD2M.linkedin