September 15, 2003
Examples of how to Calculate Different SQL Server Dates
By Gregory A. Larsen

Every now and then, you need to take the current date and calculate some other date. For instance, you might have an application that needs to determine what date is the first day of the month, or need to know the last day of the month. Now most of you probably already know how to separate the date into its piece (year, month, day, etc.) and use those pieces along with a number of functions to calculate a date that you might need. In this article, I will be showing how to use just the DATEADD and DATEDIFF function to calculate a number of different dates you might need to use in your applications.

In order to understand these examples, let's first review the DATEDIFF and DATEADD functions. The DATEDIFF function calculates the amount of time between two dates, where the time part is based on an interval of time, such as hours, days, weeks, months, years, etc. The DATEADD function calculates a date by taking an interval of time, and adding it to a date, where the interval of time will be the same as those used by the DATEDIFF function. To find out more about the DATEDIFF and DATEADD functions, and the different intervals of time read Microsoft Books Online.

Using the DATEADD and DATEDIFF functions to calculated dates requires you to think a little differently about what it takes to convert the current date into a date you need. You must think in terms of date intervals. Such as, how many date intervals it is from the current date to the date you want to calculate. Or how many date intervals is it from today to some other date like '1900-01-01', and so on. Understanding how to look at date intervals will help you more easily understand my different date examples.

### First Day of Month

For the first example, let me show you how to get the first day of the month from the current date. Remember now, this example and all the other examples in this article will only be using the DATEADD and DATEDIFF functions to calculate our desired date. Each example will do this by calculating date intervals from the current date, and then adding or subtracting intervals to arrive at the desired calculated date. Here is the code to calculate the first day of the month:

`select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)`

Let me review how this works, by breaking this statement apart. The inner most function call "getdate()", as most of you probably already know, returns the current date and time. Now the next executed function call "DATEDIFF(mm,0,getdate())" calculates the number of months between the current date and the date "1900-01-01 00:00:00.000". Remember date and time variables are stored as the number of milliseconds since "1900-01-01 00:00:00.000"; this is why you can specify the first datetime expression of the DATEDIFF function as "0." Now the last function call, DATEADD, adds the number of months between the current date and '1900-01-01". By adding the number of months between our pre-determined date '1900-01-01' and the current date, I am able to arrive at the first day of the current month. In addition, the time portion of the calculated date will be "00:00:00.000."

The technique shown here for calculating a date interval between the current date and the year "1900-01-01," and then adding the calculated number of interval to "1900-01-01," to calculate a specific date, can be used to calculate many different dates. The next four examples use this same technique to generate different dates based on the current date.

### Monday of the Current Week

Here I use the week interval (wk) to calculate what date is Monday of the current week. This example assumes Sunday is the first day of the week.

`select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)`

If you don't want Sunday to be the first day of the week, then you will need to use a different method. Here is a method that David O Malley showed me that uses the DATEFIRST setting to set the first day of the week. This example sets Monday as the first day of the week, but by changing the DATEFIRST setting any day of the week could be the first day of the week.

`set DATEFIRST 1`
` `
`select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())`

### First Day of the Year

Now I use the year interval (yy) to display the first day of the year.

`select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)`

### First Day of the Quarter

If you need to calculate the first day of the current quarter then here is an example of how to do that.

`select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)`

### Midnight for the Current Day

Ever need to truncate the time portion for the datetime value returned from the getdate() function, so it reflects the current date at midnight? If so then here is an example that uses the DATEADD and DATEDIFF functions to get the midnight timestamp.

`select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)`

### Expanding on the DATEADD and DATEDIFF Calculation

As you can see, by using this simple DATEADD and DATEDIFF calculation you can come up with many different dates that might be valuable.

All of the examples so far only calculated the current number of date intervals between the current date and "1900-01-01," and then added the number of intervals to "1900-01-01" to arrive at the calculated date. Say you modify the number of intervals to be added, or added additional DATEADD functions that used different time intervals, or subtracted intervals instead of adding intervals; by making these minor changes you can come up with many different dates.

Here are four examples that add an additional DATEADD function to calculate the last day dates for both the current and prior intervals.

### Last Day of Prior Month

Here is an example that calculates the last day of the prior month. It does this by subtracting 3 milliseconds from the first day of the month example. Now remember the time portion in SQL Server is only accurate to 3 milliseconds. This is why I needed to subtract 3 milliseconds to arrive at my desired date and time.

`select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))`

The time portion of the calculated date contains a time that reflects the last millisecond of the day ("23:59:59.997") that SQL Server can store.

### Last Day of Prior Year

Like the prior example to get the last date of the prior year you need to subtract 3 milliseconds from the first day of year.

`select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0))`

### Last Day of Current Month

Now to get the last day of the current month I need to modify slightly the query that returns the last day of the prior month. The modification needs to add one to the number of intervals return by DATEDIFF when comparing the current date with "1900-01-01." By adding 1 month, I am calculating the first day of next month and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. Here is the TSQL to calculate the last day of the current month.

`select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0))`

### Last Day of Current Year

You should be getting the hang of this by now. Here is the code to calculate the last day of the current year.

`select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))`

### First Monday of the Month

Ok, I am down to my last example. Here I am going to calculate the first Monday of the current month. Here is the code for that calculation.

`select DATEADD(wk, DATEDIFF(wk,0,`
`            dateadd(dd,6-datepart(day,getdate()),getdate())`
`                                                                 ), 0)      `

In this example, I took the code for "Monday of the Current Week," and modified it slightly. The modification was to change the "getdate()" portion of the code to calculate the 6th day of the current month. Using the 6th day of the month instead of the current date in the formula allows this calculation to return the first Monday of the current month.

### Conclusion

I hope that these examples have given you some ideas on how to use the DATEADD and DATEDIFF functions to calculate dates. When using this date interval math method of calculating dates I have found it valuable to have a calendar available to visualize the intervals between two different dates. Remember this is only one way to accomplish these date calculations. Keep in mind there are most likely a number of other methods to perform the same calculations. If you know of another way, great, although if you do not, I hope these examples have given you some ideas of how to use DATEADD and DATEDIFF to calculate dates your applications might need.

IsLeapYear

```
CREATE FUNCTION dbo.fn_is_leap_year(@year INTEGER)
RETURNS BIT AS
BEGIN

IF @year % 400 = 0
-- Years divisible by 400 (e.g. 1600, 2000) are always leap years
RETURN 1
ELSE
BEGIN
IF @year % 100 = 0
-- Years not divisible by 400 but divisible by 100 (e.g. 1900) are never leap years
RETURN 0
ELSE
BEGIN
IF @year % 4 = 0
-- Years not divisible by 400 or 100 but divisible by 4 (e.g. 1976) are always leap years
RETURN 1
ELSE
RETURN 0
END
END

-- The following statement should never be reached (but the SQL syntax parser requires it)
RETURN 0

END

```

GetAge

```
CREATE function dbo.fn_GetAge
(@in_DOB AS datetime,@now as datetime)
returns int
as

begin
DECLARE @age int
IF cast(datepart(m,@now) as int) > cast(datepart(m,@in_DOB) as int)
SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)
else
IF cast(datepart(m,@now) as int) = cast(datepart(m,@in_DOB) as int)
IF datepart(d,@now) >= datepart(d,@in_DOB)
SET @age = cast(datediff(yyyy,@in_DOB,@now) as int)
ELSE
SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) -1
ELSE
SET @age = cast(datediff(yyyy,@in_DOB,@now) as int) - 1
RETURN @age
end

```

Date Cast

```
(CAST(DATEPART(m, BirthDate) AS VARCHAR) + '/' + CAST(DATEPART(d, BirthDate) AS VARCHAR) + '/' + CAST(DATEPART(yy, BirthDate) AS VARCHAR)) as BirthDate

OR

select cast(CONVERT(CHAR(10), order_date, 101) as datetime) order_date

```

Date Name

```
SELECT Event_id, Event_name + DATENAME(mm, Event_date) + '/' +
DATENAME(dd, Event_Date) + '/' + DATENAME(yyyy, Event_date) As
EventNameAndDate, FROM event_info

```

```

=====================================================================
calendars
http://richercomponents.com/products.aspx

http://www.walmartmovie.com/watch.php
=====================================================================

=====================================================================

declare @date datetime
set @date = getdate()

--get first day of month
select dateadd(m, datediff(m, 0, @date), 0)

--get last day of month

=====================================================================

or  first day of current month

Let me review how this works, by breaking this statement apart. The inner most function call "getdate()", as most of you probably already know, returns the

current date and time. Now the next executed function call "DATEDIFF(mm,0,getdate())" calculates the number of months between the current date and the

date "1900-01-01 00:00:00.000". Remember date and time variables are stored as the number of milliseconds since "1900-01-01 00:00:00.000"; this is why you

can specify the first datetime expression of the DATEDIFF function as "0." Now the last function call, DATEADD, adds the number of months between the

current date and '1900-01-01". By adding the number of months between our pre-determined date '1900-01-01' and the current date, I am able to arrive at the first

day of the current month. In addition, the time portion of the calculated date will be "00:00:00.000."

=====================================================================

Last Day of Prior Month

Here is an example that calculates the last day of the prior month. It does this by subtracting 3 milliseconds from the first day of the month example. Now

remember the time portion in SQL Server is only accurate to 3 milliseconds. This is why I needed to subtract 3 milliseconds to arrive at my desired date and

time.

The time portion of the calculated date contains a time that reflects the last millisecond of the day ("23:59:59.997") that SQL Server can store.

Last Day of Current Month

Now to get the last day of the current month I need to modify slightly the query that returns the last day of the prior month. The modification needs to add one to

the number of intervals return by DATEDIFF when comparing the current date with "1900-01-01." By adding 1 month, I am calculating the first day of next month

and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. Here is the TSQL to calculate the last day of the current

month.

=====================================================================
=====================================================================

Creating Dates and Date Math in T-SQL with a UDF

To me, this is a pretty handy function to have in your toolbox:

Create function MDate(@Year int, @Month int, @Day int)

returns datetime

AS

BEGIN

declare @d datetime;

set @d = dateadd(year,(@Year - 1753),'1/1/1753');

set @d = dateadd(month,@Month - 1,@d);

END

This function basically lets you build dates exactly as you can in VB using the DateSerial() function -- just pass in a year, a month, and a day.

The idea is you can use this function to perform “date math” really easily, without worrying about going over month or year borders.  Everything is relative (like

DateSerial in VB), and you can add 1 to the 12th month to get the 1st month of the following year, and things like that.

So, if @Date is a date, you can easily calculate things like:

* MDate(Year(@Date),Month(@Date),1) -- the 1st day of the month
* MDate(Year(@Date),Month(@Date)+1,1)-1  -- the last day of the month
* MDate(Year(@Date),12,31) -- the last day of the year
* MDate(Year(@Date),Month(@Date)+6,1) -- the first day of the month six months from @Date.
* MDate(Year(@Date),Month(@Date),1)-1 -- last day of previous month

Try it out -- I hope you'll find it very useful!  If nothing else, it'll make your code much shorter!  Just adding this one function to your T-SQL toolbox will allow you

to handle and manipulate dates in a much easier manner.

(Again: do not use this function if your need is for MDate(2003,13,34) to return an error -- use it if you need it to return Feb 3, 2004.)

=====================================================================
=====================================================================

First day of the next month [THIS DOESN'T QUITE WORK - MODIFIED BELOW]

This script uses the current date to calculate the first day of the next month.  This example calculates the first day of the next month two different ways.  The

first way subtracts one less than the current day of the month, from a date one month from the current date.  The DATEADD and DAY, along with the

GETDATE functions are used to accomplish this.  Be aware that this first method sets the time portion of the @Fdaynm datatime variable to the current time.

The second method used in this script to calculate the first day of the next month, uses the DATEPART function to string together a date.  This second method

will be set the time portion of the @Fdaynm datetime variable to midnight.

-- Calculate the First Day of Next Month
-- Written by: Greg Larsen Date: 04/27/2002

Declare @Fdaynm datetime -- First Day of Next Month

-- Set the @Fday to the first day of the next month
-- By adding a month to the current date and then
-- subtracting the number of days from the calculated date next month and then add 1
-- This keeps the existing time

-- Print the @Fdaynm
select @Fdaynm

-- Another Way although this sets the time to 00:00:00.000
set @Fdaynm = cast(
cast(datepart(year,dateadd(month,1,getdate())) as char(4)) + '-' +
as datetime)

select @Fdaynm

```

FIRST DAY OF FOLLOWING MONTH - MODIFIED VERSION (THIS WORKS)
```

CREATE FUNCTION dbo.fn_FirstDay_FollowingMonth
-- Calculate the First Day of Next Month after selected date
(@SelectedDate as datetime)
RETURNS datetime
AS
BEGIN
Declare @ReturnDate datetime
Declare @IsLeapYear as integer
Declare @MinusDays int
Declare @SelYearNum int
Declare @SelMonthNum int
Declare @SelDayNum int
Declare @Fdaynm datetime
Set @SelYearNum = Year(@SelectedDate)
Set @SelMonthNum = Month(@SelectedDate)
Set @SelDayNum = Day(@SelectedDate)
--	Set @IsLeapYear = CovenantTest5.dbo.fn_is_leap_year(@SelYearNum)

/* -------------------------------------- */

-- check for leap years
BEGIN
IF @SelYearNum % 400 = 0
-- Years divisible by 400 (e.g. 1600, 2000) are always leap years
Set @IsLeapYear = 1
ELSE
BEGIN
IF @SelYearNum % 100 = 0
-- Years not divisible by 400 but divisible by 100 (e.g. 1900) are never leap years
Set @IsLeapYear = 0
ELSE
BEGIN
IF @SelYearNum % 4 = 0
-- Years not divisible by 400 or 100 but divisible by 4 (e.g. 1976) are always leap years
Set @IsLeapYear = 1
ELSE
Set @IsLeapYear = 0
END
END
-- The following statement should never be reached (but the SQL syntax parser requires it)
-- horsefeathers
--	  Set @IsLeapYear = 0
END

/* -------------------------------------- */

IF @SelMonthNum = 1
IF @IsLeapYear = 1
IF @SelDayNum = 31
Set @MinusDays = 3
ELSE IF @SelDayNum = 30
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE
IF @SelDayNum = 31
Set @MinusDays = 4
ELSE IF @SelDayNum = 30
Set @MinusDays = 3
ELSE IF @SelDayNum = 29
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE IF @SelMonthNum = 2
Set @MinusDays = 1
ELSE IF @SelMonthNum =  3
IF @SelDayNum = 31
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE IF @SelMonthNum =  4
Set @MinusDays = 1
ELSE IF @SelMonthNum =  5
IF @SelDayNum = 31
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE IF @SelMonthNum =  6
Set @MinusDays = 1
ELSE IF @SelMonthNum =  7
Set @MinusDays = 1
ELSE IF @SelMonthNum =  8
IF @SelDayNum = 31
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE IF @SelMonthNum =  9
Set @MinusDays = 1
ELSE IF @SelMonthNum =  10
IF @SelDayNum = 31
Set @MinusDays = 2
ELSE
Set @MinusDays = 1
ELSE IF @SelMonthNum =  11
Set @MinusDays = 1
ELSE IF @SelMonthNum =  12
Set @MinusDays = 1
ELSE
Set @MinusDays = 1

Return @Fdaynm

END

=====================================================================
=====================================================================
=====================================================================

First day of the current month

This script sets a datetime variable to the first day of the current month.  This is accomplished two different ways.  The first way is to take the current date and

subtract one more than the current day of the month.  This is done by using the DAY and DATEADD functions.  Note that by using this method the datetime

variable (@Fdaycm) will contain the first day of the month, but the time portion will be the current time.  The second way shown sets the @Fdaycm time portion

to midnight.

This script sets a datetime variable to the first day of the current month.  This is accomplished two different ways.  The first way is to take the current date and

subtract one more than the current day of the month.  This is done by using the DAY and DATEADD functions.  Note that by using this method the datetime

variable (@Fdaycm) will contain the first day of the month, but the time portion will be the current time.  The second way shown sets the @Fdaycm time portion

to midnight.
-- Calculate the First Day of Current Month
-- Written by: Greg Larsen  Date: 04/27/2002

Declare @Fdaycm datetime -- First Day of Current Month

-- Set the @Fdaycm to the first day of the current month
-- By subtracting one less then the number of the current day.
-- Note this formulat keeps the existing time as part of
-- the first day of the month.

-- Print the @Fdaycm
select @Fdaycm

-- Another Way which sets the time to 00:00:00.000
set @Fdaycm = cast (
cast(datepart(year,getdate()) as char(4)) + '-' +
substring(convert(char(2),getdate(),101),1,2) + '-01'
as datetime)

-- Print the @Fdaycm
select @Fdaycm

=====================================================================

Another method to Calculate the First Day of the Month

To example how this example works lets breaking apart the following statement  The inner most  function call “getdate()”, as you probably already know, returns

the current date and time.   Now the next executed function call “DATEDIFF(mm,0,getdate())” calculates the number of months between the current date and

the date “1900-01-01 00:00:00.000”.  Remember date and time variables are stored as the number of milliseconds since “1900-01-01 00:00:00.000”, this is why

you can specify the first datetime expression of the DATEDIFF function as “0”.  Now the last function call, DATEADD, adds the number of months between the

current date and ‘1900-01-01”.  By adding the number of months between our pre-determined date ‘1900-01-01’ and the current date, I am able to arrive at the

first day of the current month.  Also the time portion of the calculated date will be “00:00:00.000”.
-- Another method to Calculate the First Day of the Month
-- Written by: Gregory A. Larsen Date: 9/24/2003

=====================================================================

Last day of the current month

This script uses the current date to calculate the last of the current month.  This script uses the DATEPART function to take the date parts (year, month, day)

and string them together to build the last day of the current month.  While taking the dates apart it also uses the DATEADD function to days and months from

the current date.

The second part of this example also allows you to set different dates to verify the correct last day of the current month is calculated.
-- Calculate the Last Day of Current Month
-- Written by: Greg Larsen Date: 04/27/2002

Declare @Ldaycm datetime -- Last Day of Current Month

-- Set the @Ldaycm to the last day of the current month
-- By getting the first day of the month and then
-- subtracting one day
cast(
cast(datepart(year,getdate()) as char(4)) + '-' +
substring(convert(char(2),getdate(),101),1,2) + '-01'
as datetime)))

-- Print last day of current month
select @Ldaycm

-- This is here so you can test with any date
Declare @ldayx datetime

-- set @ldayx to what ever date you want to test
set @ldayx = cast ('2002-05-16' as datetime)

-- Calculate date of the last day of specified month
cast(
cast(datepart(year,@ldayx) as char(4)) + '-' +
substring(convert(char(2),@ldayx,101),1,2) + '-01'
as datetime)))

-- Print the @Ldaycm
select @Ldaycm

=====================================================================

First day of the prior month

This script uses the current date to calculate the first day of the prior month.  This is accomplished two different ways.  The first way this is done is by taking

the current date and subtracting one month using the DATEADD function, then taking that date and subtracting one less then the current day of the month.

Keep in mind that this first way will set the time part of the @Fdaypm variable to the current time.  The second way shows a method to set the time part of the

@Fdaypm variable to midnight.
-- Calculate the First Day of Prior Month
-- Written by: Greg Larsen Date: 04/27/2002

Declare @Fdaypm datetime -- First Day of Prior Month

-- Set the @Fdaypm to the first day of the prior month
-- By subtracting a month from the current date and then
-- subtracting the number of days from the calculated prior month date and then add 1
-- This keeps the existing time

-- Print the @Fdaynm
select @Fdaypm

-- Another Way although this sets the time to 00:00:00:00.
set @Fdaypm = cast(
cast(datepart(year,dateadd(month,-1,getdate())) as char(4)) + '-' +
as datetime)

select @Fdaypm

=====================================================================

Last day of the prior month

This script uses the current date to calculate the last day of the prior month.  This script uses the DATEPART function to take the date parts (year, month, day)

and string them together to build the last day of the current month.  While taking the dates apart it also uses the DATEADD function to days and months from

the current date.

The second part of this example also allows you to set different dates to verify the correct last day of the current month is calculated.
-- Calculate the Last Day of Prior Month
-- Written by: Greg Larsen DateL 4/27/2002

Declare @Ldaypm datetime -- Last Day of Prior Month

-- Set the @Ldaypm to the last day of the prior month
-- By getting the first day of the month and then subtracting one day
cast(
cast(datepart(year,getdate()) as char(4)) + '-' +
cast(datepart(mm,getdate()) as char(2)) + '-01'
as datetime)
)
select @Ldaypm

-- This is here so you can test with any date
Declare @ldayx datetime
set @ldayx = cast ('2000-03-16' as datetime)
cast(
cast(datepart(year,@ldayx) as char(4)) + '-' +
cast(datepart(mm,@ldayx) as char(2)) + '-01'
as datetime)
)

-- Print the @Lday
select @Ldaypm

=====================================================================

Last day of the next month

This script uses the current date to calculate the last day of the next month. This example calculates the first day of the month two months from the current

month, and then subtracts 1 day from that. The DATEPART function, along with the DATEADD function to accomplish this.  The second part of this script

allows you to set the @ldayx variable to any valid date to test this method of calculating the last day of the next month.
-- Calculate the Last Day of Next Month
-- Written by: Greg Larsen Date: 4/27/2002

Declare @Ldaynm datetime -- Last Day of Current Month

-- Set the @Ldaynm to the last day of the next month
-- Calculate the first date of current month + 2 months, then subtract 1 day.
cast(
cast(datepart(year,getdate()) as char(4)) + '-' +
as datetime)
)
select @Ldaynm

-- This is here so you can test with any date
Declare @ldayx datetime
set @ldayx = cast ('2001-07-16' as datetime)
cast(
cast(datepart(year,@ldayx) as char(4)) + '-' +
as datetime)
)

-- Print the @Ldaynm
select @Ldaynm

=====================================================================

Monday of the Current Week

This example uses the DATEADD and DATEDIFF functions to calculate the first day of the week.  The DATEDIFF function is used to calculate the number of

week between the current date and ‘1900-01-01.  The calculated number of weeks is then added to ‘1900-01-01 with the DATEADD function.  This returns the

first day of the current week.
-- Monday of the Current Week
-- Written by: Gregory A. Larsen Date: 9/24/2003

=====================================================================

This example uses the DATEADD and DATEDIFF functions to calculate the first day of the year.  The DATEDIFF function is used to calculate the number of

years between the current date and ‘1900-01-01.  The calculated number of years is then added to ‘1900-01-01 with the DATEADD function.  This returns the

first day of the current year.
-- First Day of the Year
-- Written by: Gregory A. Larsen Date: 9/24/2003