Posted By

MMDeveloper on 06/22/09


Tagged

mssql between dates udf


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

sulfurito


MS SQL Generate All Date and Times Between 2 Dates


 / Published in: SQL
 

URL: http://blog.pengoworks.com/index.cfm/2009/1/7/Dynamically-generating-a-table-of-dates-in-MSSQL-2005

I did not write this. Simple UDF to generate all date/times between 2 given date/times

  1. CREATE FUNCTION generateDateTable
  2. (
  3. -- Add the parameters for the function here
  4. @start_date datetime
  5. , @end_date datetime
  6. , @datepart varchar(20) = 'day'
  7. , @step int = 1
  8. )
  9. returns @dates TABLE
  10. (
  11. -- Add the column definitions for the TABLE variable here
  12. start_date datetime,
  13. end_date datetime
  14. )
  15. AS
  16. begin
  17. -- if we're doing calculations based on the days (not time) then strip the time out
  18. IF( @datepart IN ('year', 'yy', 'yyyy', 'quarter', 'qq', 'q', 'month', 'mm', 'm', 'dayofyear', 'dy', 'y', 'day', 'dd', 'd', 'week', 'wk', 'ww') )
  19. begin
  20. SET @start_date = cast(floor(cast(@start_date AS float)) AS datetime)
  21. SET @end_date = cast(floor(cast(@end_date AS float)) AS datetime)
  22. end
  23.  
  24. declare @new_start datetime
  25.  
  26. while @start_date <= @end_date
  27. begin
  28. -- get the new starting row
  29. SET @new_start = (case
  30. when @datepart IN ('year', 'yy', 'yyyy') then dateadd(yy, @step, @start_date)
  31. when @datepart IN ('quarter', 'qq', 'q') then dateadd(qq, @step, @start_date)
  32. when @datepart IN ('month', 'mm', 'm') then dateadd(mm, @step, @start_date)
  33. when @datepart IN ('dayofyear', 'dy', 'y') then dateadd(dy, @step, @start_date)
  34. when @datepart IN ('day', 'dd', 'd') then dateadd(dd, @step, @start_date)
  35. when @datepart IN ('week', 'wk', 'ww') then dateadd(ww, @step, @start_date)
  36. when @datepart IN ('hour', 'hh') then dateadd(hh, @step, @start_date)
  37. when @datepart IN ('minute', 'mi', 'n') then dateadd(n, @step, @start_date)
  38. when @datepart IN ('second', 'ss', 's') then dateadd(s, @step, @start_date)
  39. when @datepart IN ('millisecond', 'ms') then dateadd(ms, @step, @start_date)
  40. else dateadd(dd, @step, @start_date)
  41. end)
  42.  
  43. -- insert a new row
  44. INSERT
  45. @dates
  46. (
  47. start_date
  48. , end_date
  49. ) VALUES (
  50. @start_date
  51. -- since MSSQL is only accurate to 3.33ms, we need to subtract 3 ms to get the upper range in time
  52. , dateadd(ms, -3, @new_start)
  53. )
  54.  
  55. -- update the starting row
  56. SET @start_date = @new_start
  57. end
  58.  
  59. RETURN
  60. end
  61.  
  62.  
  63.  
  64.  
  65.  
  66.  
  67. SELECT
  68. *
  69. FROM
  70. dbo.generateDateTable('12-01-2008', '12-31-2008', 'day', 1)
  71. /*
  72.   Since the UDF uses a CTE to generate the data, if you're trying to output
  73.   a resultset that produces more than 100 rows, you'll want to use the OPTION
  74.   directive to set the maximum recursion number to use. This is only required
  75.   if your output would generate more than 100 records.
  76. */
  77. OPTION (maxrecursion 32767)

Report this snippet  

You need to login to post a comment.