By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,220 Members | 1,682 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Essential SQL Server Date, Time and DateTime Functions

P: 13
Standard Date and Time Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

They are simple, easy, and brief and you should use them any time you need to incorporate any date literals or date math in your T-SQL code.

Expand|Select|Wrap|Line Numbers
  1. create  function DateOnly(@DateTime DateTime)
  2. -- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
  3. returns datetime
  4. as
  5.     begin
  6.     return dateadd(dd,0, datediff(dd,0,@DateTime))
  7.     end
  8. go
  10. create function Date(@Year int, @Month int, @Day int)
  11. -- returns a datetime value for the specified year, month and day
  12. -- Thank you to Michael Valentine Jones for this formula (see comments).
  13. returns datetime
  14. as
  15.     begin
  16.     return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
  17.     end
  18. go
  20. create function Time(@Hour int, @Minute int, @Second int)
  21. -- Returns a datetime value for the specified time at the "base" date (1/1/1900)
  22. -- Many thanks to MVJ for providing this formula (see comments).
  23. returns datetime
  24. as
  25.     begin
  26.     return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
  27.     end
  28. go
  30. create function TimeOnly(@DateTime DateTime)
  31. -- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
  32. returns datetime
  33. as
  34.     begin
  35.     return @DateTime - dbo.DateOnly(@DateTime)
  36.     end
  37. go
  39. create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
  40. -- returns a dateTime value for the date and time specified.
  41. returns datetime
  42. as
  43.     begin
  44.     return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
  45.     end
  46. go
Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

Expand|Select|Wrap|Line Numbers
  1.    *  where TransactionDate >= dbo.Date(2005,1,2)  -- no formatting or implicit string conversions needed for date literals
  3.     * select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.
  5.     * select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.
Introducing TimeSpans to SQL Server

With those functions in place, we can add two more that will give us further flexibility when working with dates and times: The concept of a "TimeSpan", very similar to what is available in the .NET framework.

Expand|Select|Wrap|Line Numbers
  1. create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
  2. -- returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
  3. returns datetime
  4. as
  5.     begin
  6.     return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
  7.     end
  9. create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
  10. -- returns the # of units specified in the TimeSpan.
  11. -- The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
  12. returns int
  13. as
  14.     begin
  15.     return case @Unit
  16.         when 'd' then datediff(day, 0, @TimeSpan)
  17.         when 'h' then datediff(hour, 0, @TimeSpan)
  18.         when 'm' then datediff(minute, 0, @TimeSpan)
  19.         when 's' then datediff(second, 0, @TimeSpan)
  20.         else Null end
  21.     end
Here, a TimeSpan is just a datetime offset from the "base" date of 1/1/1900. Creating one is the same as creating a Time using the Time() function, but we have added a parameter for Days to give more flexibility.

The TimeSpanUnits() function works similar to standard T-SQL DatePart() function, but it returns the total # of units in the given time span. So, if you create a time span of 1 day and 2 hours, then TimeSpanUnits("d") will return 1 and TimeSpanUnits("h") will return 26. Negative values can be returned as well. You also may wish to implement the TimeSpanUnits() function as multiple functions, one per unit (e.g., TimeSpanHours(), TimeSpanDays(), etc) depending on your preference.

Of course, a simple way to create a TimeSpan is to simply subtract two standard T-SQL DateTimes. Also please note that we can add and subtract Dates, Times, and TimeSpans all together using standard + and - operators and everything will work as expected. We can also add integers to our Dates and Times which will add entire days to the values.

Here's a TimeSpan usage example:

Expand|Select|Wrap|Line Numbers
  1. declare @Deadline datetime -- remember, we still use standard datetimes for everything, include TimeSpans
  2. set @Deadline = dbo.TimeSpan(2,0,0,0)   -- the deadline is two days
  4. declare @CreateDate datetime
  5. declare @ResponseDate datetime
  7. set @CreateDate = dbo.DateTime(2006,1,3,8,30,0)  -- Jan 3, 2006, 8:30 AM
  8. set @ResponseDate = getdate() -- today
  10. -- See if the response date is past the deadline:
  11. select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result
  13. -- Find out how many total hours it took to respond:  
  14. declare @TimeToRepond datetime
  15. set @TimeToRespond = @ResponseDate - @CreateDate
  17. select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours
  19. -- Return the response time as # of days, # of hours, # of minutes:
  20. select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes
  22. -- Return two days and two hours from now:
  23. select getdate() + dbo.TimeSpan(2,2,0,0)
Aug 15 '08 #1
Share this Article
Share on Google+