November 27, 2010 at 12:05 pm
Comments posted to this topic are about the item DateCeiling Function
November 28, 2010 at 9:45 pm
A useful script, that is nearly there, but not quite.
select dbo.dateceiling('2010-11-29 18:00:00.000','hh')
should return 2010-11-29 18:00:00.000 in the same way that select ceiling(1.00) returns 1
However, it doesn't - it returns 2010-11-29 19:00:00.000
Looking at the code, the same would be true for minutes & seconds where the rest of the datetime was zero.
A small, but potentially important, bug.
November 29, 2010 at 8:07 am
I feel it's easier truncating a CONVERT(..., ..., 121), then CASTing again and adding the unit if the two dates differ - you can easily add a flag for ceiling/truncating:
CREATE FUNCTION dbo.DATECEILING( @f1 AS datetime, @part AS varchar(2), @is_ceiling AS bit )
RETURNS datetime
AS
BEGIN
DECLARE @s1 varchar(21), @s2 varchar(21), @f2 datetime
select @s1 = REPLACE( CONVERT( varchar(23), @f1, 121 ), '-', '' )
IF @part = 'yy' SET @s2 = LEFT( @s1, 4 ) + '0101'
IF @part = 'mm' SET @s2 = LEFT( @s1, 6 ) + '01'
IF @part = 'dd' SET @s2 = LEFT( @s1, 8 )
IF @part = 'hh' SET @s2 = LEFT( @s1, 12 ) + '00:00'
IF @part = 'mi' SET @s2 = LEFT( @s1, 15 ) + '00'
IF @part = 'ss' SET @s2 = LEFT( @s1, 17 )
SET @f2 = CAST( @s2 as datetime )
IF @is_ceiling = 1 And @f1 > @f2
BEGIN
IF @part = 'yy' SET @f2 = DATEADD( year, 1, @f2 )
IF @part = 'mm' SET @f2 = DATEADD( month, 1, @f2 )
IF @part = 'dd' SET @f2 = DATEADD( day, 1, @f2 )
IF @part = 'hh' SET @f2 = DATEADD( hour, 1, @f2 )
IF @part = 'mi' SET @f2 = DATEADD( minute, 1, @f2 )
IF @part = 'ss' SET @f2 = DATEADD( second, 1, @f2 )
END
RETURN @f2
END
February 19, 2014 at 1:15 pm
Its interesting to see how other people solve the same types of problems.
Usually when the business calls for a GROUP BY around date/time components I see something like (in this case, grouping around CCYYMM):
Group By Replace(Convert(VarChar(7),[YourDateTimeColumn],121),'-','')
I came up with this implementation (which also does the "ceiling" part properly as per antony-688446's comment:-)):
Use [YourDatabaseName];
If Object_Id('dbo.DateCeiling','fn') is not Null Drop function dbo.DateCeiling;
Go
Create function dbo.DateCeiling
(
@dt DateTime,
@Part VarChar(max)
)
Returns DateTime
With SchemaBinding,Returns Null on Null Input
As Begin
Declare
@CCYYMMDD DateTime,
@Floor DateTime,
@Inc Int;
Set @CCYYMMDD=DateAdd(dd,DateDiff(dd,0,@dt),0);
Set @Floor=
Case @Part
when 'yy' then DateAdd(yy,DateDiff(yy,0,@dt),0)
when 'yyyy' then DateAdd(yyyy,DateDiff(yyyy,0,@dt),0)
when 'm' then DateAdd(m,DateDiff(m,0,@dt),0)
when 'mm' then DateAdd(mm,DateDiff(mm,0,@dt),0)
when 'd' then DateAdd(d,DateDiff(d,0,@dt),0)
when 'dd' then DateAdd(dd,DateDiff(dd,0,@dt),0)
when 'hh' then DateAdd(hh,DateDiff(hh,@CCYYMMDD,@dt),@CCYYMMDD)
when 'n' then DateAdd(n,DateDiff(n,@CCYYMMDD,@dt),@CCYYMMDD)
when 'mi' then DateAdd(mi,DateDiff(mi,@CCYYMMDD,@dt),@CCYYMMDD)
when 's' then DateAdd(s,DateDiff(s,@CCYYMMDD,@dt),@CCYYMMDD)
when 'ss' then DateAdd(ss,DateDiff(ss,@CCYYMMDD,@dt),@CCYYMMDD)
else Null
End;
If @Floor is Null Return Null;
Set @Inc=
Case Cast(Cast(@dt as VarBinary) as BigInt)-Cast(Cast(@Floor as VarBinary) as BigInt)
when 0 then 0
else 1
End;
Return
Case @Part
when 'yy' then DateAdd(yy,@Inc,@Floor)
when 'yyyy' then DateAdd(yyyy,@Inc,@Floor)
when 'm' then DateAdd(m,@Inc,@Floor)
when 'mm' then DateAdd(mm,@Inc,@Floor)
when 'd' then DateAdd(d,@Inc,@Floor)
when 'dd' then DateAdd(dd,@Inc,@Floor)
when 'hh' then DateAdd(hh,@Inc,@Floor)
when 'n' then DateAdd(n,@Inc,@Floor)
when 'mi' then DateAdd(mi,@Inc,@Floor)
when 's' then DateAdd(s,@Inc,@Floor)
when 'ss' then DateAdd(ss,@Inc,@Floor)
End;
End
Go
-- Unit Tests
Declare @dt DateTime;
Set @dt=GetDate();
Select
@dt[DateTime],
dbo.DateCeiling(@dt,'yy')[Year],
dbo.DateCeiling(@dt,'mm')[Month],
dbo.DateCeiling(@dt,'dd')[Day],
dbo.DateCeiling(@dt,'hh')[Hour],
dbo.DateCeiling(@dt,'mi')[Minute],
dbo.DateCeiling(@dt,'ss')[Second],
dbo.DateCeiling(Null,'ss')[Null Test 1],
dbo.DateCeiling(@dt,Null)[Null Test 2],
dbo.DateCeiling(Null,Null)[Null Test 3];
If this functionality were needed in a large row set operation I would implement its internals in a CROSS APPLY or as stacked/cascaded sub-queries (for significantly increased performance).
February 19, 2014 at 1:52 pm
Uuups, on my previous comment I had to make a change to the unit tests (after the UDF definition). I didn't use the variable @dt in all cases where I should have...
May 18, 2016 at 7:00 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply