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