SQL Server Date, Time and DateTime Functions

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)
returns datetime
as
begin
return @DateTime - dbo.DateOnly(@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.