TSQL strip date from datetime

Question

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?

Answer 1

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

Answer 2

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)

Answer 3

If you're using SQL 2008

SELECT CAST(GETDATE() AS TIME)