Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

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

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

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

Feedback

Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Handy T-SQL Date Function

Jeff,

Very nice and useful function.

Thanks!

EEEEK!
12/17/2003 8:07 PM | EEEEK
Gravatar

# re: Handy T-SQL Date Function

Thanks Jeff. Works like a dream.
5/10/2004 9:24 AM | Wayne
Gravatar

# 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
Gravatar

# 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
Gravatar

# re: Handy T-SQL Date Function

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


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

# 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
Gravatar

# 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
Gravatar

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

Thanks. Works great for me!
6/30/2005 2:01 AM | Mooch
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

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

Wonderful!
1/11/2006 12:10 PM | Monk
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

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

Yes it is very useful
7/26/2006 3:23 AM | Ml
Gravatar

# 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...
Gravatar

# 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
Gravatar

# 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
Gravatar

# 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
Gravatar

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

PERFECT!!!
6/24/2010 12:36 PM | RICK
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET