I want to get the Time from a SQL Server DateTime column. I want to only time between calculation.Date not important only important hour,minute and seconds

My query:

Select case when cast(time,workstart) between '07:15:00' and '08:30:00' then 08:00:00 end ,Personelnum 
from table;

Should be Result:

How can I do this?

CodePudding user response：

cast(time,workstart) is not the correct syntax. A cast operation is cast(value as type), so it should be cast(workstart as time). This is basic stuff you should be able to do on your own.

The other issue is you have to add the time back to base date value.

I'd tend solve this by converting to seconds since midnight, like this:

dateadd(second, case when datediff(second, cast(workstart as date), workstart) between 26100 and 30600 then 28800 else datediff(second, cast(workstart as date), workstart)  end, cast(cast(workstart as date) as datetime))

This is because I've been working with SQL since long before the time data type existed, and because time is explicitly defined as a time of day and not as a timespan, meaning it's still a little weird to compose a datetime result from separate date and time values (you have to make sure they are both to datetime values first):

cast(cast(workstart as date) as datetime)   cast(case when cast(workstart as time(0)) between '07:15:00' and '08:30:00' then '08:00:00' else cast(workstart as time(0)) end as datetime) as TimeOfDayFinal

See them both work here:

https://dbfiddle.uk/z8EGuDkH

The link also shows how I found the seconds values to use in the first sample.

CodePudding user response：

As long as you're using SQL Server 2012 or newer you can use the TIME datatype to get what you're looking for.

DECLARE @PunchClock TABLE (PunchID INT IDENTITY, WorkStart DATETIME, PersonelNum INT)
INSERT INTO @PunchClock(WorkStart, PersonelNum) VALUES
('2022-01-01 07:35:13.000', 13021),
('2022-01-01 08:12:15.000', 17058),
('2022-01-01 07:14:59.000', 12345),
('2022-01-01 08:30:01.000', 12345)

SELECT DATEADD(SECOND,CASE WHEN CAST(WorkStart AS TIME) BETWEEN '07:15' AND '08:30' THEN DATEDIFF(SECOND,CAST(WorkStart AS TIME),'08:00') ELSE 0 END,WorkStart) AS WorkStart, PersonelNum
  FROM @PunchClock

WorkStart               PersonelNum
-----------------------------------
2022-01-01 08:00:00.000 13021
2022-01-01 08:00:00.000 17058
2022-01-01 07:14:59.000 12345
2022-01-01 08:30:01.000 12345

This case expression converts the DATETIME to a TIME and produces the DATEDIFF in SECONDS to your specified time when it falls within your range. We then just added the difference is seconds to the DATETIME from the table. I added an extra couple of rows to show how rows outside of the range are not affected.