Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DateCeiling Function Expand / Collapse
Author
Message
Posted Saturday, November 27, 2010 12:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
Comments posted to this topic are about the item DateCeiling Function
Post #1026993
Posted Sunday, November 28, 2010 9:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:03 PM
Points: 262, Visits: 294
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.
Post #1027124
Posted Monday, November 29, 2010 8:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
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

Post #1027362
Posted Wednesday, February 19, 2014 1:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:13 PM
Points: 188, Visits: 424
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).




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1543197
Posted Wednesday, February 19, 2014 1:52 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:13 PM
Points: 188, Visits: 424
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...



PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1543211
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse