Search code examples
sql-serverdatetimetimedateadd

SQL Server: How to check a time period whether falls in a period and deduct out the time interval?

Good day All.

My task is to check whether the times fall in into the breaktime period. If yes, I deduct out the break time.

Since the breaktimes are different for different periods, I do use Date X to check which break time to use for calculation.

The parameter I passed in are Time A (2019-11-28 10:30:00.000), Time B (2019-11-28 11:30:00.000) and Date X (20191128)

In my database, there are

•Effective From, VARCHAR(YYYYMMDD, 20191127)

•Effective To, VARCHAR(YYYYMMDD, 20191131)

•Break Time 1 Start Time, VARCHAR (HHMMSS, 104500)

•Break Time 1 Duration, INT (30) ... till Break time N.

I don't have a column named Break Time n End Time which I need to determine myself by adding Break Time n Duration to Break Time n Start.

So far what I have tried is to

•convert 104500 to 10:45:00 using left, substring and right function

•add 30 minutes using DATEADD(minutes, 30, 10:45:00)

But the return is 1990-01-01 11:15:00.000

I can't compare 1990-01-01 11:15:00.000 with 2019-11-28 10:30:00.000 to check and calculate the time interval.

Any suggestions to overcome this? Really appreciate.

Solution

  • cast it as time so you can compare.

    select cast('2019-11-28 10:30:00.000' as time), cast('1990-01-01 11:15:00.000' as time)

    Output:

    enter image description here

    adding your time to your date.

    declare @time varchar(10) ='104500' 
select cast(concat(cast('20191127' as date), ' ', left(@time,2)+ ':' + substring(@time,3,2) + ':' + right(@time,2)) as datetime)