InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 21:15:22 GMT20http://www.sqlservercentral.com/Forums/Topic1021921-2765-1.aspxThis is not the best way to implement this -- See http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server[code="sql"]declare @datetime datetime;set @datetime = getdate();select @datetime;select dateadd(year,datediff(year,0,@datetime),0);select dateadd(month,datediff(month,0,@datetime),0);select dateadd(day,datediff(day,0,@datetime),0);select dateadd(hour,datediff(hour,0,@datetime),0);select dateadd(minute,datediff(minute,0,@datetime),0);select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');[/code]or converted to this function:[code="sql"]create function dbo.DATEFLOOR ( @seed datetime, @part varchar(2))returns datetimeasbegin/*Sample Usage (uses standard dateparts): select 'second', dbo.datefloor(getdate(),'ss') union allselect 'minute', dbo.datefloor(getdate(),'mi') union all select 'hour' , dbo.datefloor(getdate(),'hh') union allselect 'day' , dbo.datefloor(getdate(),'dd') union allselect 'month' , dbo.datefloor(getdate(),'mm') union allselect 'year' , dbo.datefloor(getdate(),'yy') */select @retDate = casewhen @part = 'ss' then dateadd(second,datediff(second,'2000-01-01',@seed),'2000-01-01')when @part = 'mi' then dateadd(minute,datediff(minute,0,@seed),0)when @part = 'hh' then dateadd(hour,datediff(hour,0,@seed),0)when @part = 'dd' then dateadd(day,datediff(day,0,@seed),0)when @part = 'mm' then dateadd(month,datediff(month,0,@seed),0)when @part = 'yy' then dateadd(year,datediff(year,0,@seed),0)endreturn @retDateend[/code]Thu, 29 Dec 2011 11:51:56 GMTHogan Longhttp://www.sqlservercentral.com/Forums/Topic1021921-2765-1.aspxYes, you will have a scalar computation versus a datatype conversion - slightly more expensive. This was more for load functions where rounding down to the nearest hour or minute was the goal, or trimming miliseconds (as developers will often ask). Cast/convert date to string is probably still recommended for rounding down to the nearest day. This is more of a pattern to round down to other parts. The cost is realized in CPU at load, and the benefit is realized in post-processing when doing aggregations to the sub-day grain without further scalar computations at report time.Wed, 17 Nov 2010 06:04:51 GMTgklundthttp://www.sqlservercentral.com/Forums/Topic1021921-2765-1.aspxWon't the query cost for select dbo.datefloor(getdate(),'dd')be a lot higher than say select convert(datetime, convert(char(10), getdate(), 120))?Wed, 17 Nov 2010 00:13:54 GMTPer Sidenhttp://www.sqlservercentral.com/Forums/Topic1021921-2765-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/71568/">DateFloor Function</A>[/B]Tue, 16 Nov 2010 22:26:28 GMTgklundt