SQL Server Tutorial - Date functions

1. Get month names with month numbers:

In this query we will learn about How to get all month names with month number in SQL Server. I have used this query to bind my dropdownlist with month name and month number.

1 2 3 4 5 6 7 8 9 10 11 12

;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())) ;

---

2. Get name of current month:

In this query we will learn about How to get name of current month in SQL Server.

1	select DATENAME(MONTH, GETDATE()) AS CurrentMonth

---

3. Get name of day:

In this query we will learn about How to get name of day in SQL Server.

1	select DATENAME(WEEKDAY, GETDATE()) AS TodayIs

---

4. Get first date of current month:

In this query we will learn about How to first date of current month in SQL Server.

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

---

5. Get last date of current month:

In this query we will learn about How to last date of current month in SQL Server.

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

---

6. Get first date of previous month:

In this query we will learn about How to first date of previous month in SQL Server.

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

---

7. Get last date of previous month:

In this query we will learn about How to last date of previous month in SQL Server.

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

---

8. Get first date of current year:

In this query we will learn about How to first date of current year in SQL Server.

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

---

9. Get last date of current year:

In this query we will learn about How to last date of current year in SQL Server.

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

---

10. Select date without time:

In this query we will learn about How to select date without time in SQL Server.

1	SELECT CONVERT(date, getdate()) AS DateWithoutTime

---

11. Select records within two dates:

In this query we will learn about How to select records within two dates in SQL Server.

1	SELECT * FROM EMPLOYEE  WHERE CONVERT(date, CreatedOn) BETWEEN '1/1/2015' AND '12/31/2016'

Note: Here Employee is my table name and CreatedOn is a name of date column used for filtering.

---

12. Calculate age from birth date:

In this query we will learn about How to calculate age from birth date in SQL Server.

1 2 3 4 5 6 7 8 9 10 11 12

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

---

13. Get days between two dates:

In this query we will learn about How to get days between two dates in SQL Server.

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