April 4, 2012 at 7:50 pm
ColdCoffee (4/4/2012)
True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..
I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2012 at 7:51 pm
schillingt (4/4/2012)
I had already dropped the miliseconds due to this issue. I agree that I should drop the time stamp however even though all our date fields are defined as DATETIME and the selection logic seemed to work the same both ways with and without the 23:59:59 appended.Thank you all for the help oin this.
See Lynn's post for the correct way to handle date ranges regardless of the date datatype.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2012 at 7:53 pm
Jeff Moden (4/4/2012)
ColdCoffee (4/4/2012)
True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.
Yes Jeff, i agree with u and Lynn, but OP specifically posted that he needed milliseconds, if you could see his code, he was CONVERTing to varchar and appending Milliseconds, so i thot he was really needing the milliseconds part. But still, i should also have made a note in my post, first place! Thanks for the pointers, Jeff and Lynn!
April 4, 2012 at 10:24 pm
ColdCoffee (4/4/2012)
Jeff Moden (4/4/2012)
ColdCoffee (4/4/2012)
True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..I'm a bit surprised by that answer, CC. I thought you knew to always use the next time period and use >= and < for date comparisons instead of trying to shoehorn in a time.
Yes Jeff, i agree with u and Lynn, but OP specifically posted that he needed milliseconds, if you could see his code, he was CONVERTing to varchar and appending Milliseconds, so i thot he was really needing the milliseconds part. But still, i should also have made a note in my post, first place! Thanks for the pointers, Jeff and Lynn!
Understood. I always try to protect people that think they need milliseconds. 🙂 They just don't know what a mistake the ol' 23:59:59.xxx thing is and they don't understand the BETWEEN with the use of DATETIMEs is a form of "Death by SQL".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2012 at 10:44 pm
{edit} Had a bug in the comments (believe it or not) I posted. I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2012 at 6:34 am
I had a bug in the "usage" comments in the code I previously posted. I also took the time to add a usage comment very specific to this thread.
schillingt (4/3/2012)
Trying to put together a genericprocedure orfunction that I can pass a date and some parametes to and have it return a start and end date that I can put into @StartDate and @EndDate for use in my select statements in a larger multi part procedure.
With the understanding that you should almost always use the method that Lynn showed in his post for creating criteria for a range of dates or times, the following function should do it for you. As is normal with my code, the details for usage and a couple of other things are in the comments in the code.
CREATE FUNCTION dbo.CreateDateTimeSpan
/**********************************************************************************************************************
Purpose:
Given any legal DATETIME and a standard 2 character DATEPART, return the begining and next date/time of that DATEPART.
When the DATEPART is WK (week), a day offset to determine the starting day for the week will be applied.
Usage Examples:
SELECT SpanType, StartDate, NextStartDate
FROM dbo.CreateDateTimeSpan(@pDateTime, @pDatePart, @pDOWStart);
SELECT st.selectlist, timespan.SpanType, timespan.StartDate, timespan.NextStartDate
FROM dbo.sometable st
CROSS APPLY dbo.CreateDateTimeSpan(st.somedatetimecolumn, @pDatePart, @pDOWStart) timespan;
--===== Find the bounds of the current week starting on Sunday
SELECT @StartDate = StartDate,
@NextStartDate = NextStartDate
FROM dbo.CreateDateTimeSpan(GETDATE(), 'wk', 7);
Notes:
1. The 3rd operand will only be used when @pDatePart = 'WK'and should be 1 for all other DATEPARTs.
2. If an illegal date or datepart is given, the function will throw the following error.
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type datetime.
3. Please see notes in code for legal DATEPARTs and values for @pDOWStart.
4. This code does not rely on any DATEFIRST or language settings unless you're using the Hijri calendar.
Since I've never used that calendar, I don't know how to code for it.
Revision History:
Rev 00 - 04 Apr 2012 - Jeff Moden - Initial creation
**********************************************************************************************************************/
--===== Declare the IO parameters
(
@pDateTime DATETIME,
@pDatePart CHAR(2), --ss=Second, mi=Minute, hh=Hour, dd=Day, wk=Week, mm=Month, qq=Quarter, yy=Year
@pDOWStart INT --1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteOffset AS
(--==== Calculate the reference date to use.
-- For Week calculations, the @pDOWStart will offset the day.
-- For time calculations, the current day will be used to prevent overflows.
-- Won't affect other calculations until the last week of the year 9999.
SELECT RefDate =
CAST(
CASE
WHEN @pDatePart IN ('dd','wk','mm','qq','yy')
THEN -53690+((@pDOWStart-1)%7) -- 1753-01-01 = -53690, a Monday
WHEN @pDatePart IN ('hh','mi','ss')
THEN DATEADD(dd,DATEDIFF(dd,0,@pDateTime),0)
END
AS DATETIME)
)
SELECT SpanType = @pDatePart,
StartDate =
CASE @pDatePart
WHEN 'ss' THEN DATEADD(ss,DATEDIFF(ss,RefDate,@pDateTime),RefDate)
WHEN 'mi' THEN DATEADD(mi,DATEDIFF(mi,RefDate,@pDateTime),RefDate)
WHEN 'hh' THEN DATEADD(hh,DATEDIFF(hh,RefDate,@pDateTime),RefDate)
WHEN 'dd' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime),RefDate)
WHEN 'wk' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)/7*7,RefDate)
WHEN 'mm' THEN DATEADD(mm,DATEDIFF(mm,RefDate,@pDateTime),RefDate)
WHEN 'qq' THEN DATEADD(qq,DATEDIFF(qq,RefDate,@pDateTime),RefDate)
WHEN 'yy' THEN DATEADD(yy,DATEDIFF(yy,RefDate,@pDateTime),RefDate)
ELSE CAST(-99999 AS DATETIME) --Throw an error if the datepart is wrong
END,
NextStartDate =
CASE @pDatePart
WHEN 'ss' THEN DATEADD(ss,DATEDIFF(ss,RefDate,@pDateTime)+1,RefDate)
WHEN 'mi' THEN DATEADD(mi,DATEDIFF(mi,RefDate,@pDateTime)+1,RefDate)
WHEN 'hh' THEN DATEADD(hh,DATEDIFF(hh,RefDate,@pDateTime)+1,RefDate)
WHEN 'dd' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)+1,RefDate)
WHEN 'wk' THEN DATEADD(dd,DATEDIFF(dd,RefDate,@pDateTime)/7*7+7,RefDate)
WHEN 'mm' THEN DATEADD(mm,DATEDIFF(mm,RefDate,@pDateTime)+1,RefDate)
WHEN 'qq' THEN DATEADD(qq,DATEDIFF(qq,RefDate,@pDateTime)+1,RefDate)
WHEN 'yy' THEN DATEADD(yy,DATEDIFF(yy,RefDate,@pDateTime)+1,RefDate)
END
FROM cteOffset
;
The "/7*7" thing may look strange but it's just integer math for rounding the date down to the beginning of a week.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply