Amending a field/value based on BETWEEN value in different column

Transact-SQL
1

I have an issue where by I have two columns, one is a date field, one is a time field. The two don't have a relationship and are seperate values. If the time elapses over midnight my date doesnt update accordingly so i end up with erroneous data.

In a SQL query, I want to say the following:-

IF timevalue BETWEEN 00:00 AND 06:00 THEN datevalue +1

I'm not sure what the syntax should be for this. Any help is appreciated.

2

If you are sure the date value is always 1 day behind for times less than 06:00:

Declare @testData table (date_column date, time_column time(0));
 Insert Into @testData (date_column, time_column)
 Values ('20220227', '23:00')
      , ('20220227', '01:00')
      , ('20220227', '06:00')
      , ('20220228', '07:00')
      ;

 Select *
      , actual_date = dateadd(day, iif(td.time_column <= '06:00', 1, 0), td.date_column)
      , full_datetime = cast(td.date_column As datetime) + cast(td.time_column As datetime) + iif(td.time_column <= '06:00', 1, 0)
      , full_datetime2 = dateadd(day, datediff(day, 0, dateadd(day, iif(td.time_column <= '06:00', 1, 0), td.date_column)), cast(td.time_column As datetime2))
   From @testData td;

And we can 'simplify' this to:

 Select *
      , actual_date = dateadd(day, t.is_next_day, td.date_column)
      , full_datetime = cast(td.date_column As datetime) + cast(td.time_column As datetime) + t.is_next_day
      , full_datetime2 = dateadd(Day, datediff(day, 0, dateadd(day, t.is_next_day, td.date_column)), cast(td.time_column As datetime2))
   From @testData td
  Cross Apply (Values (iif(td.time_column <= '06:00', 1, 0))) As t(is_next_day);
3

You should use a trigger to do this rather than in every query that runs against the table.

What specifically does "If the time elapses over midnight" mean? Are you adding to the time to cause it go over midnight? Or do you always want to add a day if the time is between 00:00 and 06:00.

4

My date field is a shift date, this remains fixed.
My time field is a booking time so what I mean is that on tonight's shift which is 1st March 2022, there are booking times that fall after midnight.
So, shift date may be 1st March but the booking time may be 01:00 on 2nd March

My time field is time only i.e. it is not a DATETIME so that's why I'm wanting to use some logic to adjust the date dependent on the value in the time field

5

So how do you know that someone did not start their shift at 01:00 or 05:00? If someone clocks in 1 minute early for their 6am shift start - what is going to happen?

Many potential issues with this design I think.

6

Hi Jeff, as I mentioned in my previous post, the shift date is always fixed.

I specify the shift date in the SQL query so the shift date is whatever I specify and as such is a fixed value.

7

That really doesn't answer the question though - doesn't matter what you specify in the query. If there is data in the table where that persons shift started at 5am - or someone clocked out after 6am then the calculations will be incorrect.

The only way to determine if the date should be incremented would be to compare to the shift start time. If the shift start time is greater than the shift end time - add 1 to the shift date to get the end date for that shift. If the start time is less than the end time - then no need to increment as shift start and end all occurred on the same day.

8

The shift date does not relate to the times in my query so the logic I've explained will work.

The dates are merely labels rather than be a 'real' piece of data.

9

Okay - then the solutions I provided should work.