Log in  ::  Register  ::  Not logged in

## DateFloor Function

 Author Message gklundt Old Hand Group: General Forum Members Points: 364 Visits: 140 Comments posted to this topic are about the item DateFloor Function Per Siden Grasshopper Group: General Forum Members Points: 22 Visits: 94 Won't the query cost for select dbo.datefloor(getdate(),'dd')be a lot higher than say select convert(datetime, convert(char(10), getdate(), 120))? gklundt Old Hand Group: General Forum Members Points: 364 Visits: 140 Yes, 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. Hogan Long SSC Rookie Group: General Forum Members Points: 32 Visits: 3 This is not the best way to implement this -- See http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server`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');`or converted to this function:`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` Iwas Bornready One Orange Chip Group: General Forum Members Points: 29348 Visits: 885 Thanks for the script.