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).