SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gordon Klundt / DateCeiling Function / Latest Posts

RE: DateCeiling Function

dbuendiab — Mon, 29 Nov 2010 08:07:06 GMT

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:[code="sql"]CREATE FUNCTION dbo.DATECEILING( @f1 AS datetime, @part AS varchar(2), @is_ceiling AS bit )RETURNS datetimeASBEGINDECLARE @s1 varchar(21), @s2 varchar(21), @f2 datetimeselect @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 > @f2BEGIN 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 )ENDRETURN @f2END[/code]

RE: DateCeiling Function

antony-688446 — Sun, 28 Nov 2010 21:45:39 GMT

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 1However, it doesn't - it returns 2010-11-29 19:00:00.000Looking 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.

DateCeiling Function

gklundt — Sat, 27 Nov 2010 12:05:36 GMT

Comments posted to this topic are about the item [B]DateCeiling Function[/B]