If a shift goes past midnight, how do I add 1 day onto the date

Transact-SQL
1

Hi,

If end shift goes past midnight, how do I add 1 day to the date.

I have shift start date, start time and end time.

If a member of staff works 21:00-05:00 however on shift start date is 13-12-2015 and I need end date to show 14-12-2015.

I need help with a formula that will work for a number of shift patterns.

many thanks

2

How you would do depends on how the data is stored in the database table. If the start is stored as datetime - for example, '2015-12-15T21:00:00.00' then, and the number of working hours is stored as an int, you would simply add the number of hours to startdate, for example. DATEADD(hour, 8, StartDateTime)

3

Hi,

They are both datetime datatypes.

I have various shift patterns such at 09:00-13:00, 09:00-17:00, 10:00-18:00, 18:00-02:00, 21:00-05:00, 21:30-05:30, 01:00-07:00.

Thank you

4

Hi
Let us assume your start date & time is 12-DEC-2015, 21:00:00 and Workout this query to add 8 hours of time

SELECT TO_CHAR(
TO_DATE('12-DEC-2015, 21:00:00', 'DD-MON-YYYY, HH24:MI:SS')+1/3,'DD-MON-YYYY, HH24:MI:SS') End_Time
FROM Dual

5

I didn't understand what you meant by "both". If you can post sample data as it exists in your table that would help. Usually converting to character and doing the calculations on date/time is not a good approach.

If you need help posting, see here.

6

Start Time and End Time are both datatypes.

I have one column start time and one column end time.

I know what i want but just don't know how to achieve it. :confused:

If a member of staff works past midnight then add 1 onto the roster date.

Thank you for your help :relaxed:

7

It should be a simple query which most people who frequent on this forum should be able to write for you easily. But they can't do that because they don't know what your data structure is.

Is there one table, or more than one table involved? You said start time and end time are data types.
There is no untyped or dynamically typed data in SQL (if you set aside variants), so the fact that they are data types does not give any info.
Is there a staff table? with staff ID perhaps?

At the very least, provide some sample data from your tables and then type out some tabular data that you want to get based on that sample input data.

8

You need to post some sample data for people to be able to help you on this one. Create a simple CREATE TABLE statement where the columns mimic your real table and provide some insert statements to populate the table.