Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Make Date function (like in VB)

Author  Topic 

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2002-12-11 : 17:12:14
I hate manipulating dates in SQL. One of the many things Access and VB handles much better!

I wanted a function like DateSerial(Year,Month,Day) so I created one.

One caveat: The Year must be >= 1800. But all other numbers can be most any int value that results in a valid date.

Which means you can do:

MDate(Year(@d),1,1) (first day of date @d)
MDate(Year(@d), Month(@d) + 1, -1) (last day of month of date @d)
MDate(2000,1,1) (create a date quickly and easily w/o convert)
MDate(Year(@d)+1,1,1) (get first day of next year for date @d)

..etc... whatever you can do with VB's DateSerial() function, except for the year must be >=1800.

Or, does this exist already in SQL and I'm missing it somewhere??

Is there an easier/better way to do this?

* * * *

Create function MDate(@Year int, @Month int, @Day int)
returns datetime
AS
BEGIN
declare @d datetime;
set @d = dateadd(year,(@Year - 1800),'1/1/1800');
set @d = dateadd(month,@Month - 1,@d);
return dateadd(day,@Day - 1,@d)
END

- Jeff

Edited by - jsmith8858 on 12/12/2002 14:04:03

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-12-11 : 17:52:54
You can change the minimum year to 1753, but you need to do some checking so that people can't put in a negative month or day value that can calculate out to a date earlier than January 1, 1753. They could do that now with:

SELECT MDate(1800, -39000, -18000)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2002-12-12 : 14:01:45
yeah.. i thought of that. Couldn't think of a quick easy way to do the check (I'm not too good with error checking in T-SQL).

Any ideas?

Thanks!

- Jeff
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2002-12-12 : 15:40:16
Use the ABS function to check if Month and Day are positive.
Use the raiserror function to generate an error.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-12-12 : 16:05:36
How about

Create function MDate(@Year int, @Month int, @Day int)
returns datetime
AS
BEGIN
return convert (datetime, convert(varchar(4),@Year) +
right('00' + convert(varchar(2),@Month),2) +
right('00' + convert(varchar(2),@Day),2)
)
end


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2002-12-12 : 18:19:27
Thanks for the tips -- I actually WANT to allow negative Months and Days, like in the examples I gave.

That way you can do things like:

MDate(2000,Month(@Date) - 6, 1)

to get the first day of the month 6 months before date, without worrying the month of the @Date parameter is less than 7.

Just like DateSerial in VB.

That's also why I didn't concatenate a string together to be converted -- I wanted a nice flexible function you can do "date math" with.

- Jeff
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-05-24 : 18:35:31
There was a lot of discussion of this subject on the thread from the link below, and I did some testing of various ways to do this. I took the method that tested fastest, and put it into a modified version of Jeff's function.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471



create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(day,@Day-1,dateadd(month,((@Year-1900)*12)+@Month-1,0))
end


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-01-27 : 16:32:37
I found a slightly faster method with shorter code, so here is an updated version of the function.

I posted test results using the modified code here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471

create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
end




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-12 : 08:09:32
Maybe nitpicking, but excluding the paranthesis makes the function a 1-2 percent faster

declare @year smallint,
@month smallint,
@day smallint

select @year = 2007,
@month = 2,
@day = 12

select dateadd(month, 12 * @Year - 22800 + @Month - 1, @Day - 1)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-02-12 : 09:45:31
Is it removing the parenthesis, or changing it from multiplication to subtraction that made the difference?

Might as well shorten it even more:

create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
return dateadd(month,(12*@Year)-22801+@Month,@Day-1)
end


Edit:
Changed constant from 22799 to 22801.

Edit:
Added back parenthesis for clarity of operator precedence.

CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-12 : 09:48:24
Haven't made any test for the difference why it is faster.
If you replace 22799 with 22801, the query works very fast and returns the correct result.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-02-12 : 10:40:45
I did some testing, and saw a small but consistent difference on my desktop on SQL 2005 Developer Edition between the older version with multiplication and the newer version with addition only. Having or not having parenthesis did not seem to make any difference.

The performance difference on my tests was from 2.5% to 4.8% with inline code.



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-02-12 : 10:54:18
With your timetest code here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471
I got consistent better times with 4.3 to 5.1 percent faster on my laptop (SQL 2005 Developer Edition 9.00.3033)

Really wierd since the "original"
return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
has 3 subtractions, 1 addition and 1 multiplication.

And the most recent version
return dateadd(month, 12 * @Year - 22801 + @Month, @Day - 1)
has 2 subtractions, 1 addition and 1 multiplication.

I first didn't believe that reducing 1 simple arithmetic operation could do a 5% difference in execution time.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-02-12 : 11:46:30
The algorithim has already been highly optimized, so removing an additional operation is bound to have an impact, small as it is.

The last change I made (removing the outer DATEADD) only reduced the runtime about 14%.

It's hard to imagine that we will find a simpler algorithim than four arithmetic operations and one DATEADD call, so maybe this is the limit.



CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-12-03 : 01:55:56
This is a little off topic, but I thought I would post this as a solution to the problem of finding the last day of the month, given the year and month as integers.

The code uses a slightly modified version of the algorithim for conveting year, month, and day to datetime that I posted before on this thread.


select
	*,
	LastDayOfMonth = dateadd(month,(12*[Year])-22800+[Month],-1)
from
	(--Test Data
	select [Year] = 1753, [Month] = 1	union all
	select [Year] = 2011, [Month] = 1	union all
	select [Year] = 2011, [Month] = 2	union all
	select [Year] = 2011, [Month] = 3	union all
	select [Year] = 2011, [Month] = 4	union all
	select [Year] = 2011, [Month] = 5	union all
	select [Year] = 2011, [Month] = 6	union all
	select [Year] = 2011, [Month] = 7	union all
	select [Year] = 2011, [Month] = 8	union all
	select [Year] = 2011, [Month] = 9	union all
	select [Year] = 2011, [Month] = 10	union all
	select [Year] = 2011, [Month] = 11	union all
	select [Year] = 2011, [Month] = 12	union all
	select [Year] = 2012, [Month] = 1	union all
	select [Year] = 2012, [Month] = 2	union all
	select [Year] = 9999, [Month] = 12
	) a


Year        Month       LastDayOfMonth
----------- ----------- -----------------------
       1753           1 1753-01-31 00:00:00.000
       2011           1 2011-01-31 00:00:00.000
       2011           2 2011-02-28 00:00:00.000
       2011           3 2011-03-31 00:00:00.000
       2011           4 2011-04-30 00:00:00.000
       2011           5 2011-05-31 00:00:00.000
       2011           6 2011-06-30 00:00:00.000
       2011           7 2011-07-31 00:00:00.000
       2011           8 2011-08-31 00:00:00.000
       2011           9 2011-09-30 00:00:00.000
       2011          10 2011-10-31 00:00:00.000
       2011          11 2011-11-30 00:00:00.000
       2011          12 2011-12-31 00:00:00.000
       2012           1 2012-01-31 00:00:00.000
       2012           2 2012-02-29 00:00:00.000
       9999          12 9999-12-31 00:00:00.000




CODO ERGO SUM
Go to Top of Page

victy23
Starting Member

3 Posts
Posted - 2011-12-18 : 03:26:12
How does this work ?
unspammed
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site