Full date given week date part and year
-
Does anybody know if a UDF that will return the full date given a week of year and the year?
I am speaking of the datepart() week. So given year 2006 and week 14. How can I get the full date? e.g. April 2, 2006
Or get full date from dayofyear.
I can get day of year using this formula but it doesnt get me that much closer.
(wk#*7)-(dow of week of Jan 1)
So the 14th week would be (14*7)-1 = 97
Is there a native function or a udf for converting week or day of year into a date?
Thanks,
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Doh! Nevermind, if I have dayOfYear its a simply dateadd() from Jan 1 to get the date I am after.
Well sometime it helps to write down my question so that I can figure it out
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
OK to answer my own question more fully, here are two UDF's I created to return the start and end of a week given a week number and a year. They are designed for bounding all times in a given week so the weekEnd returns as "[date] 23:59:59".
I am sure greater minds could write better functions but these will do for me.
CREATE FUNCTION [dbo].[udf_weekstart]
(
@wk int,
@year int
)
RETURNS datetime AS
BEGIN
declare @DW int
declare @dy int
declare @weekstart datetime
set @DW = datepart(dw,'Jan 1, ' + cast(@year as varchar(4)))
select @dy = (@wk*7)
set @weekstart = dateadd(wk,-1,dateadd(d,@dy,'Jan 1, ' + cast(@year as varchar(4))))
cast(@year as varchar(4))))
return @weekstart
END
CREATE FUNCTION [dbo].[udf_weekend]
(
@wk int,
@year int
)
RETURNS datetime AS
BEGIN
declare @DW int
declare @dy int
declare @weekEnd datetime
set @DW = datepart(dw,'Jan 1, ' + cast(@year as varchar(4)))
select @dy = (@wk*7)
set @weekend = dateadd(d,-1,dateadd(d,@dy,'Jan 1, ' + cast(@year as varchar(4))))
set @weekend = dateadd(s,-1,dateadd(d,1,@weekend))
return @weekend
END
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Very nice... very nice indeed...
These might execute a bit quicker... in fact, considering that they're single line formulas, you may not want to use them as functions, at all.. just use the formulas...
CREATE FUNCTION dbo.udf_WeekStart (@Year INT, @Week INT) RETURNS DATETIME AS BEGIN RETURN DATEADD(wk,@Week,DATEADD(yy,@Year-1900,0)) END
CREATE FUNCTION dbo.udf_WeekEnd (@Year INT, @Week INT) RETURNS DATETIME AS BEGIN RETURN DATEADD(ms,-3,DATEADD(wk,@Week+1,DATEADD(yy,@Year-1900,0))) END
Hope these help...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, I'll probably replace mine with yours, thanks for that.
Tell me, is there a performance hit for wrapping a formula like this in a function? In some large queries it helps sometimes to call it as a function rather then an inline formula, but if there is a significant performance hit I may stop that practice.
Cheers
Dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Dave,
Very volitile subject... I got involved with a pretty big discussion on this subject before and I'll just say that, in all the "million-row" testing I did, I satisfied myself that properly constucted UDF's are usually just as fast (sometimes, even faster) as equivalent in-line code and that the benefits of using them (self-documenting because of their name if properly named, faster development because you don't have to look-up complicated formulas, consistency because they always do the same thing, simplified testing because they've already been tested if you do it right, readability of the code, etc, etc) make them worth using even if there is a minor performance hit on some of them.
To summarize, I very successfully use UDF's for this type of thing all the time and the only reason I mentioned using the formulas directly was to offer a second option if you needed it.
As a side-bar, one caveat you may wish to avoid is calling one function within another... I haven't tested the performance of nested UDF's but they're a real pain in the patootie to troubleshoot if something goes wrong just as deeply nested single record stored procs can be.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Great, thanks for that, I think I will continue using UDF's for code readability and consistancy. In the current app I am working on I need to copnvert varchar dates to datetimes, convert to and from UTC allot, and expand to proper full day bounding. The functions sure make the code read better
I found that nesting UDF did sometimes make things harder to debug so I already try not to do that.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
You shouldn't necessarily need a function to convert a VARCHAR date to a datetime to save in a table... it's one of those "instrinsic conversions" that happen auto-magically if the format is something that SQL recognizes. The exception to that rule is if the varchar date is in the format of dd/mm/yyyy instead of mm/dd/yyyy.
If the proper full day bounding you are talking about is similar to the week bounding in your previous post, consider this instead of using 23:59:59.997 as part of the end-bound (misses 3 milliseconds of the day, requires a bit of extra coding in the UDF making it a bit slower, etc)...
In a literal format, let's say that you want to find all records from yesterday that have a datetime column... you could do this (in your previous post, it would require 2 functions)...
SELECT * FROM yourtable WHERE somedatecol BETWEEN DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0) AND DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)+'23:59:59.997'
In your previous post, each formula would be represented by a function, one taking longer than the other because of the handling of the time component.
If you do it like the following, you would only need 1 function, there would be no time component, and you wouldn't miss the 3 milliseconds (min resolution of datetime)...
SELECT * FROM yourtable WHERE somedatecol >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0) --Yesterday at midnight AND somedatecol < DATEADD(dd,DATEDIFF(dd,0,GETDATE() ),0) --Today at midnight
There's not much extra coding (column name listed twice), one less function is required, it'll run faster because we don't need to add the 23:59:59.997 time, and there's no chance of missing something that may come in as a varchar time in the missing 3 milliseconds.
If the function looked like this...
CREATE FUNCTION dbo.fWholeDate(@MyDate DATETIME)
RETURNS DATETIME
AS
-- This function removes the time element from a datetime (sets it to midnight)
BEGIN
RETURN DATEADD(dd,DATEDIFF(dd,0,@MyDate),0)
END
...Then the SQL would look like this...
SELECT * FROM yourtable WHERE somedatecol >= dbo.fWholeDate(GETDATE()-1) --Yesterday at midnight AND somedatecol < dbo.fWholeDate(GETDATE() ) --Today at midnight
A hidden benefit is that the function will automatically convert VARCHAR dates provided that they are in the format SQL recognizes as stated before.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just a follow up... I did some million row testing on the simple functions we created here...
On a quiet multiprocessor box, the functions returned about 12% faster than the inline code but was more expensive on CPU time used and disk I/O. Duration for the million row test on the function was about 8 seconds.
On a quiet single processor box, the inline code returned neary twice as fast as the function and was still less expensive on CPU time used and disk I/O. Duration for the million row test on the inline code was about 10 seconds.
The bottom line, especially on such simple functions, is that you have to do some load testing so you can make an intelligent decision as to whether or not to use the function. That decision making process includes those other hidden benefits of self documentation, consistancy, and readable code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply