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

<< A Recursive User-Defined Function (SQL Server 2000) | Home | Create a Date in T-SQL >>

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

Update: Please refer to this page for an update on this concept; I recommend that you use the newer functions presented on that page.

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

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

returns datetime

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

Print | posted on Tuesday, December 09, 2003 10:50 AM | Filed Under [ T-SQL DateTime Data ]

Feedback

# re: Handy T-SQL Date Function

Very handy Jeff.

Just one suggestion, why not call the function DateSerial!

12/9/2003 5:12 PM | DavidM

# re: Handy T-SQL Date Function

Honestly? because after adding in the dbo prefix and all that, i felt the function's name would just be too damn long!!! :)

12/9/2003 5:31 PM | Jeff

# re: Handy T-SQL Date Function

Jeff,

Very nice and useful function.

Thanks!

EEEEK!

12/17/2003 8:07 PM | EEEEK

# re: Handy T-SQL Date Function

Thanks Jeff. Works like a dream.

5/10/2004 9:24 AM | Wayne

# re: Handy T-SQL Date Function

How do you call if from query analyzer?

MDate(Year(getdate()),Month(getdate()),1)

would the above work?

6/3/2004 2:15 PM | Dennis

# re: Handy T-SQL Date Function

you must prefix all UDF's with the owner in SQL 2000, not sure exactly why. even those owned by dbo. So, in Query Analyzer, you would just write:

select dbo.MDate( ..etc...)

6/3/2004 3:33 PM | JeffS

# re: Handy T-SQL Date Function

good, But Give soime more function realted to the same ,

8/19/2004 8:43 AM | AK singh

# re: Handy T-SQL Date Function

Dosn't work for me when try to create it as stored procedure, any ideas

8/31/2004 8:58 AM | vivian

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

Amazing works for every date combination
Made my life easier

6/29/2005 7:53 AM | Bharath Pammi Kumar

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

Thanks. Works great for me!

6/30/2005 2:01 AM | Mooch

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

Great function, works like a charm!

10/25/2005 9:36 AM | Are Riksaasen

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

This is great, it saved me a ton of coding and it's very easy to use.

12/1/2005 4:18 PM | Chris Jankow

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

Hi man i an to find out end date from starting date
help me pls.

12/2/2005 4:59 AM | shrikant

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

Hi HEINRICH GLOVER

I am working on 1Mil record table and need to get date overlapping functionality, by using ur sp i could pass four values to SP, and i need to delare cursors for selecting these date. Considering size of table this is very much time consuming process. Do you have any other alternative ?

Regards
Shrikant

12/14/2005 5:31 AM | Shrikant

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

Wonderful!

1/11/2006 12:10 PM | Monk

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

That was EXACTLY what I was looking for

THanks a million
-Tim

1/21/2006 1:47 PM | Tim

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

Thanks for the help. It's amazing how the simplest things can be the most useful

3/19/2006 3:00 AM | Thanks for the script

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

Thanks a million Jeff. This function came very handy and useful in various queries.

Ajay Desai

5/23/2006 2:18 PM | Ajay M Desai

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

yes, thank you this will be a big help

6/7/2006 5:47 PM | kurt schroeder

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

Thanks, it has been a good help i've used it like this

select DATEADD ( M, 1, DATEADD ( D, -1, ( DATEADD ( M, MONTH(GETDATE()) , dateadd(year,(YEAR(GETDATE()) - 1753),'1/1/1753') ) ) ) )

It can be included in a query. It gives the last day of the present month

6/20/2006 10:16 AM | imayal

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

I'm using Visual Studio .NET to create reports for use with MS SQL Server 2000 Reporting Services. I'd like to use your code to set the default value for input parameters to a report. I don't know much about the behind-the-scenes stuff with SQL Server, such as createing stored procedures. I do know where to put custom code into a report though. Can I just copy your code as it is onto the code tab of the report properties page?

The two parameters I want to set the default values for are @BegDate and @EndDate - they should be set to the first and last days of the previous month. (IE if I'm running the report today, 7/7/06, I want BegDate = 6/1/06 and EndDate = 6/30/06) I still want the ability to enter in any dates, but these will most likely be the dates that need to be used. Thanks so much!

7/7/2006 10:14 AM | jcat_3

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

Yes it is very useful

7/26/2006 3:23 AM | Ml

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

This is great, thanks a a ton!!

8/29/2006 5:55 PM | shortii...

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

Thanks! Your function helped so much. My company was thinking of implementing a function to get Six Year Start (one month, six years ago), One Year Start (one month, one year ago), and Last Date (one year ago), with the days all being on the 15th. So I wrote this function which uses your function at the end of it! I can call it like this select dbo.rollingdates(getdate(), 'Last') Thanks again!

Create Function dbo.rollingdates(@GetDate datetime, @Rolling_Ind varchar(255))

Returns datetime
as begin
declare @GetYear int
declare @GetMonth int

select @GetMonth = (case @Rolling_Ind
when 'One' then (month(@GetDate))
when 'Six' then (month(@GetDate))
when 'Last' then (case month(@GetDate)
when '12' then '11'
when '11' then '10'
when '10' then '09'
when '09' then '08'
when '08' then '07'
when '07' then '06'
when '06' then '05'
when '05' then '04'
when '04' then '03'
when '03' then '02'
when '02' then '01'
when '01' then '12'
end)
end)

select @GetYear = (case @Rolling_Ind
when 'One' then (year(@GetDate)-1)
when 'Six' then (year(@GetDate)-6)
when 'Last' then (year(@GetDate))
end)

declare @GetDay int
select @GetDay = '15' --day(@GetDate)

Return dbo.makedate(@GetMonth,@GetDay,@GetYear) -- makes the date a date, recursive to makedate function!!

/*
Create function MakeDate(@Month int, @Day int, @Year 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
*/

end

-- I put your function in my function in case anything happens to it... :)

7/27/2007 2:27 PM | Dan

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

Thanks a lot Jeff. This helps me a lot to overcome most of my TSQL date Functions

9/8/2009 11:07 PM | Isura Malagala

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

Well the function seems to set up correctly... but how do you call it???

9/15/2009 10:14 AM | Matt

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

PERFECT!!!

6/24/2010 12:36 PM | RICK

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

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.

10/6/2010 10:14 AM | snow boots for women

Comments have been closed on this topic.

Jeff's SQL Server Blog

My Links

Advertisement

News

Archives

Post Categories

Programming

Sports

SQL