**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.

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.

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

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

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

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)

Ever need to truncate the
time portion for the datetime value returned from the getdate()
function, so it reflects the current date at

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

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.

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.

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

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

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

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 6^{th} day of the current month.
Using the 6^{th} day of the month instead of the current date in the
formula allows this calculation to return the first Monday of the current
month.

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 select dateadd(m, datediff(m, 0, dateadd(m, 1, @date)), -1) ===================================================================== or first day of current 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." ===================================================================== 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 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)) ===================================================================== ===================================================================== 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); return dateadd(day,@Day - 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 -- All rights reserved Copyright 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 Set @Fdaynm = dateadd(day,-1*(day(getdate())-1),dateadd(month,1,getdate())) -- 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)) + '-' + cast(datepart(mm,dateadd(month,1,getdate())) as char(2)) + '-01' as datetime) select @Fdaynm

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 Set @Fdaynm = dateadd(day,-1*(day(@SelectedDate)-@MinusDays),dateadd(month,1,@SelectedDate)) 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 -- All rights reserved copywrite 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. Set @Fdaycm = dateadd(day,day(getdate())*-1+1,getdate()) -- 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 -- All rights reserved Copyright 2003 select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) ===================================================================== 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 -- All rights reserved Copyright 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 set @Ldaycm = dateadd(day,-1,dateadd(month,1, 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 set @Ldaycm = dateadd(day,-1,dateadd(month,1, 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 -- All rights Reserved Copyright 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 Set @Fdaypm = dateadd(day,(day(getdate())-1)*-1,dateadd(month,-1,getdate())) -- 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)) + '-' + cast(datepart(mm,dateadd(month,-1,getdate())) as char(2)) + '-01' 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 -- All rights reserved Copyright 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 set @Ldaypm = dateadd(day,-1, 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) set @Ldaypm = dateadd(day,-1, 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 -- All rights reserved Copyright 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. set @Ldaynm = dateadd(day,-1, cast( cast(datepart(year,getdate()) as char(4)) + '-' + cast(datepart(mm,dateadd(month,2,getdate())) as char(2)) + '-01' 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) set @Ldaynm = dateadd(day,-1, cast( cast(datepart(year,@ldayx) as char(4)) + '-' + cast(datepart(mm,dateadd(month,2,@ldayx)) as char(2)) + '-01' 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 -- All rights reserved Copyright 2003 select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) ===================================================================== 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 -- All rights reserved Copyright 2003 select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) ===================================================================== NUMBER OF DAYS IN A GIVEN MONTH select datediff(dd, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0), dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, +1)))