SQL Server And Dates

sqlserver

Dates are one of the things I find myself googling more than anything else when writing queries, so I thought I would write a post with a few examples on how to get different combinations of date for use in your queries.

Let’s say we have a date and we want to get just the month, or the week or even just the year, how can we do that?

    
      SELECT 
      DATEPART(month,GETDATE()) AS [Month],
      DATEPART(wk,GETDATE()) AS [Week],
      DATEPART(year,GETDATE()) AS [Year]
    

You see, the Month, Week & Year have been split from the DATETIME column and returned as their seperate values.

Let’s see what other manipulation we can do with dates

First Day Of Current Week

     
  
    SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)) AS [FirstDayOfCurrentWeek]
   

Last Day Of Current Week

     

  SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 6)) AS [LastDayOfCurrentWeek]
 

First Day Of Last Week

     

  SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 0)) AS [FirstDayOfPeviousWeek]
 

Last Day Of Last Week

     

  SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 7, GETDATE()), 6)) AS [LastDayOfPreviousWeek]
 

First Day Of Next Week

     

  SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 7)) AS [FirstDayOfNextWeek]
 

Last Day Of Next Week

     

  SELECT CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 13)) AS [LastDayOfNextWeek]
 

First Day Of Current Month

    

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(GETDATE() - 1) ), GETDATE())) AS [FirstDayOfCurrentMonth]
 

Last Day Of Current Month

     

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE())) ), DATEADD(m, 1, GETDATE()))) AS [LastDayOfCurrentMonth]
 

First Day Of Last Month

     

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, -1, GETDATE() - 2)) ), DATEADD(m, -1, GETDATE() - 1))) AS [FirstDayOfPreviousMonth]
 

Last Day Of Last Month

     

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(GETDATE()) ), GETDATE())) AS [LastDayOfPreviousMonth]
 

First Day Of Next Month

     

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 1, GETDATE() - 1)) ), DATEADD(m, 1, GETDATE()))) AS [FirstDayOfNextMonth]
 

Last Day Of Next Month

     

  SELECT CONVERT(DATE, DATEADD(d, -( DAY(DATEADD(m, 2, GETDATE())) ), DATEADD(m, 2, GETDATE()))) AS [LastDayOfNextMonth]
 

First Day Of Current Year

     

  SELECT CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS [FirstDayOfCurrentYear]
 

Last Day Of Current Year

     

  SELECT CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)))) AS [LastDayOfCurrentYear]
 

First Day Of Last Year

     

  SELECT CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) AS [FistDayOfLastYear]
 

Last Day Of Last Year

     

  SELECT CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))) AS [LastDayOfPreviousYear]
 

First Day Of Next Year

     

  SELECT CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) AS [FirstDayOfPreviousYear]
 

Last Day Of Next Year

     

  SELECT CONVERT(DATE, DATEADD(ms, -2, DATEADD(YEAR, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0)))) AS [LastDayOfNextYear]
 

Current Year

     

  SELECT DATEPART(year,GETDATE()) AS [CurrentYear]
 

Current Month

     

  SELECT DATEPART(month,GETDATE()) AS [CurrentMonth]
 

Current Week Number

     

  SELECT DATEPART(wk,GETDATE()) AS [CurrentWeekNumber]
 

Current Day In Month

     

  SELECT DATEPART(day,GETDATE()) [CurrentDay]
 

Get Week Day Name

     

  SELECT DATENAME(weekday,GETDATE()) AS [WeekdayName]
 

Get Week Day

     

  SELECT DATEPART(weekday,GETDATE()) AS [WeekdayNumber]
 

Date 30 Days Ago

     

  SELECT DATEADD(day,-30,GETDATE()) AS [Date30DaysAgo]
 

Date 30 Days Future

     

  SELECT DATEADD(day,+30, GETDATE()) AS [Date30DaysFuture]
 

There are so many other things you can do with dates this is just the tip of the ice berg, but hopefully it will help you get started in working with dates on SQL Server.