每个SQL Server数据库都必须具备的基本日期和时间功能,以确保您可以轻松地操纵日期和时间,而根本不需要任何格式方面的考虑。
它们简单,易用且简短,您应在需要在T-SQL代码中合并任何日期文字或日期数学的任何时间使用它们。
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
请记住,调用UDF时必须在所有者(通常是dbo)之前加上前缀。
用法示例:
* 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.
将TimeSpans引入SQL Server
有了这些功能,我们可以添加另外两个功能,这些功能将为我们提供处理日期和时间时的更大灵活性:“ TimeSpan”的概念,与.NET框架中的功能非常相似。
create function TimeSpan(@Days int, @Hours int, @Minutes int, @Seconds int)
-- returns a datetime the specified # of days/hours/minutes/seconds from the "base" date of 1/1/1900 (a "TimeSpan")
returns datetime
as
begin
return dbo.Time(@Hours,@Minutes,@Seconds) + @Days
end
create function TimeSpanUnits(@Unit char(1), @TimeSpan datetime)
-- returns the # of units specified in the TimeSpan.
-- The Unit parameter can be: "d" = days, "h" = hours, "m" = minutes, "s" = seconds
returns int
as
begin
return case @Unit
when 'd' then datediff(day, 0, @TimeSpan)
when 'h' then datediff(hour, 0, @TimeSpan)
when 'm' then datediff(minute, 0, @TimeSpan)
when 's' then datediff(second, 0, @TimeSpan)
else Null end
end
在这里,TimeSpan只是相对于“基本”日期1/1/1900的日期时间偏移。
创建一个与使用Time()函数创建一个Time相同,但是我们为Days添加了一个参数,以提供更大的灵活性。
TimeSpanUnits()函数的工作方式类似于标准的T-SQL DatePart()函数,但是它返回给定时间范围内的单位总数。 因此,如果创建的时间跨度为1天2小时,则TimeSpanUnits(“ d”)将返回1,TimeSpanUnits(“ h”)将返回26。也可以返回负值。 您可能还希望将TimeSpanUnits()函数实现为多个函数,每个函数一个(例如,TimeSpanHours(),TimeSpanDays()等),具体取决于您的偏好。
当然,创建TimeSpan的一种简单方法是简单地减去两个标准的T-SQL DateTimes。 另外请注意,我们可以使用标准的+和-运算符一起添加和减去Date,Times和TimeSpans,一切将按预期工作。 我们还可以将整数添加到“日期和时间”中,从而将整天添加到值中。
这是一个TimeSpan用法示例:
declare @Deadline datetime -- remember, we still use standard datetimes for everything, include TimeSpans
set @Deadline = dbo.TimeSpan(2,0,0,0) -- the deadline is two days
declare @CreateDate datetime
declare @ResponseDate datetime
set @CreateDate = dbo.DateTime(2006,1,3,8,30,0) -- Jan 3, 2006, 8:30 AM
set @ResponseDate = getdate() -- today
-- See if the response date is past the deadline:
select case when @ResponseDate > @CreateDate + @Deadline then 'overdue.' else 'on time.' end as Result
-- Find out how many total hours it took to respond:
declare @TimeToRepond datetime
set @TimeToRespond = @ResponseDate - @CreateDate
select dbo.TimeSpanUnits('h', @TimeToRespond) as ResponseTotalHours
-- Return the response time as # of days, # of hours, # of minutes:
select dbo.TimeSpanUnits('d',@TimeToRespond) as Days, DatePart(hour, @TimeToRespond) as Hours, DatePart(minute, @TimeToRespond) as Minutes
-- Return two days and two hours from now:
select getdate() + dbo.TimeSpan(2,2,0,0)
From: https://bytes.com/topic/sql-server/insights/830111-essential-sql-server-date-time-datetime-functions