SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Make Date function (like in VB)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/11/2002 :  17:12:14  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

USA
15635 Posts

Posted - 12/11/2002 :  17:52:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 12/12/2002 :  14:01:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 12/12/2002 :  15:40:16  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 12/12/2002 :  16:05:36  Show Profile  Visit nr's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 12/12/2002 :  18:19:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 05/24/2006 :  18:35:31  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 01/27/2007 :  16:32:37  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 02/12/2007 :  08:09:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 02/12/2007 :  09:45:31  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 02/12/2007 10:44:26
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/12/2007 :  09:48:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 02/12/2007 09:50:00
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/12/2007 :  10:40:45  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 02/12/2007 :  10:54:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 02/12/2007 :  11:46:30  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 12/03/2011 :  01:55:56  Show Profile  Reply with Quote
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

China
3 Posts

Posted - 12/18/2011 :  03:26:12  Show Profile  Reply with Quote
How does this work ?
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000