SQL Server Date, Time and DateTime Functions

 

 

user defined 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)
-- 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.

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.

 

Ref : http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx