DateFloor Function

• gklundt

  Ten Centuries

  Points: 1130

  More actions

  November 16, 2010 at 10:26 pm

  #226764

  Comments posted to this topic are about the item DateFloor Function

• Per Siden

  Valued Member

  Points: 64

  More actions

  November 17, 2010 at 12:13 am

  #1251702

  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

  Ten Centuries

  Points: 1130

  More actions

  November 17, 2010 at 6:04 am

  #1251847

  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 Enthusiast

  Points: 104

  More actions

  December 29, 2011 at 11:51 am

  #1426628

  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 datetime

  as

  begin

  /*

  Sample Usage (uses standard dateparts):

  select 'second', dbo.datefloor(getdate(),'ss') union all

  select 'minute', dbo.datefloor(getdate(),'mi') union all

  select 'hour' , dbo.datefloor(getdate(),'hh') union all

  select 'day' , dbo.datefloor(getdate(),'dd') union all

  select 'month' , dbo.datefloor(getdate(),'mm') union all

  select 'year' , dbo.datefloor(getdate(),'yy')

  */

  select @retDate = case

  when @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)

  end

  return @retDate

  end

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 18, 2016 at 6:58 am

  #1879831

  Thanks for the script.