Essential SQL Server Date, Time and DateTime Functions

Essential SQL Server Date, Time and DateTime Functions

Part I: Standard Date and Time Functions

I've posted some variations of these before, but here they all are in 1 easy package:  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.  I have always wondered why T-SQL omits these basic functions, but the beauty of user defined functions is that we can create them ourselves.

create  function DateOnly(@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end
go

create function Date(@Year int, @Month int, @Day int)
-- returns a datetime value for the specified year, month and day
-- Thank you to Michael Valentine Jones for this formula (see comments).
returns datetime
as
    begin
    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)
    end
go

create function Time(@Hour int, @Minute int, @Second int)
-- Returns a datetime value for the specified time at the "base" date (1/1/1900)
-- Many thanks to MVJ for providing this formula (see comments). 
returns datetime
as
    begin
    return dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)
    end
go

create function TimeOnly(@DateTime DateTime)
-- returns only the time portion of a DateTime, at the "base" date (1/1/1900)
-- Thanks, Peso! 
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
go

create function DateTime(@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)
-- returns a dateTime value for the date and time specified.
returns datetime
as
    begin
    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)
    end
go

Remember that you must prefix UDFs with the owner (usually dbo) when calling them.

Usage Examples:

•  where TransactionDate >= dbo.Date(2005,1,2)  -- no formatting or implicit string conversions needed for date literals
• select dbo.Date(year(getdate()), 1,1) -- returns the first day of the year for the current year.
• select dbo.DateOnly(getdate()) -- returns only the date portion of the current day.
• if dbo.TimeOnly(SomeDate) = dbo.Time(5,30,0)  -- check to see if the time for a given date is at 5:30 AM
• select dbo.Date(year(getdate()), month(getdate()),1) -- returns the first day of the current month.
• select dbo.Date(year(getdate()), month(getdate())+1,0) -- returns the last day of the current month.
• where SomeDate >= dbo.DateOnly(getdate()) and SomeDate < dbo.DateOnly(getDate())+1 -- a simple way to get all transactions that occurred on the current date
• select dbo.DateOnly(getdate()) + 1 + dbo.Time(14,30,0) -- returns tomorrow at 2:30 PM.

and so on ....