SQL Server And Dates

Rich | Nov 5, 2018| Sqlserver| #Dates #SQL #Sqlserver #T-Sql

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.

See Also