TSQL strip date from datetime

问题:

What is the best way to strip the date from a DATETIME so only time is left to do a comparison?

I know I can do the following:

CONVERT(DATETIME, CONVERT(VARCHAR(8), GETDATE(),8))

But this involves convert and characters. If I wanted to check whether a time (including minutes) was between two other times stored in DATETIME columns, is there an elegant way to do this without having to rely on converting to character strings?

回答1:

Try the TimeOnly function from Essential SQL Server Date, Time, and DateTime Functions:

create function TimeOnly(@DateTime DateTime)
returns datetime
as
    begin
    return dateadd(day, -datediff(day, 0, @datetime), @datetime)
    end
go

Or simply cast the DateTime as Time in SQL Server 2008:

declare @time as time
set @time = cast(dateTimeVal as time)

回答2:

If you're using SQL 2008

SELECT CAST(GETDATE() AS TIME)

回答3:

DECLARE 
  @Now DateTime, 
  @Today DateTime,
  @Time DateTime

SET @Now = GetDate() 
SET @Today = DateAdd(dd, DateDiff(dd, 0, @Now), 0) 

SET @Time = DateAdd(ss, DateDiff(ss, @Today, @Now), 0)

SELECT @Time

回答4:

Just another way to get date and time from datetime.

datetime in SQL Server implemented as float value where whole part floor(value) is day from 1900-01-01 and fractional part (value - floor(value)) is part of twenty-four hours elapsed from start of day

select 
    d as [datetime],
    cast(cast(T.d as float) - floor(cast(T.d as float)) as datetime) as [time],
    cast(floor(cast(T.d as float)) as datetime) as [date]
from
    (values
        (getdate()),
        ('1753-01-01 23:59:59.700'),
        ('1899-12-31 00:00:00.300'),
        ('1900-01-01 00:00:00.300')
    ) as T(d);