Calculate working shift giving current datetime in sybase

Question

I need a sql script in sybase or at least ansi sql that giving the current datetime, i can get the current working shift.

• Working shift '1' begins at 08:00 and ends at 15:30
• Working shift '2' begins at 15:30 and ends at 23:00
• Working shift '3' begins at 23:00 and ends at 08:00AM of the next day

I am having trouble with the third shift, because of the change between one day and next day

SET  @FechaActual = convert(datetime, 'JUN 11 2015 11:00AM', 100) --getdate()

SET @HoraComienzoTurno1 = convert(datetime,convert(varchar, @FechaActual, 101) + " 08:00:00 AM")
SET @HoraFinTurno1 = convert(datetime,convert(varchar, @FechaActual, 101) + " 03:29:59 PM")
SET @HoraComienzoTurno2 = convert(datetime,convert(varchar, @FechaActual, 101) + " 03:30:00 PM")
SET @HoraFinTurno2 = convert(datetime,convert(varchar, @FechaActual, 101) + " 10:59:59 PM")
SET @HoraComienzoTurno3 = convert(datetime,convert(varchar, @FechaActual, 101) + " 11:00:00 PM")
SET @HoraFinTurno3 = convert(datetime,convert(varchar, @FechaActual, 101) + " 07:59:59 AM")

IF @FechaActual >= @HoraComienzoTurno1 AND @FechaActual <= @HoraFinTurno1
BEGIN
    SELECT 1 AS Turno, @FechaActual AS FechaActual
END

IF @FechaActual >= @HoraComienzoTurno2 AND @FechaActual <= @HoraFinTurno2 
BEGIN
    SELECT 2 AS Turno, @FechaActual AS FechaActual
END

IF @FechaActual >= @HoraComienzoTurno3 AND @FechaActual <= @HoraFinTurno3 
BEGIN
    SELECT 3 AS Turno, dateadd(dd, -1, @FechaActual) AS FechaActual
END

Answer 1

I have no sybase here, then cannot try it. I think what you need is datepart function. This function only gets the hours, or minutes from the datetime value, then you will be able to easily check without thinking in days.

I did the following changes:

• I changed the code to show how it works (maybe my spelling is not right)

• I changed the last condition to be an OR, because it´s impossible for the last shift worker, to have an hour > 23 and < 9 at the same time ;-)

Hope it may help you. (sorry if it doesn´t compile, but it´s a few years since I don´t play with sybase).

SET  @FechaActual = datepart("hhmi",convert(datetime, 'JUN 11 2015 11:00AM', 100))

SET @HoraComienzoTurno1 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 08:00:00 AM"))
SET @HoraFinTurno1 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 03:29:59 PM"))
SET @HoraComienzoTurno2 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 03:30:00 PM"))
SET @HoraFinTurno2 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 10:59:59 PM"))
SET @HoraComienzoTurno3 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 11:00:00 PM"))
SET @HoraFinTurno3 = datepart("hhmi",convert(datetime,convert(varchar, @FechaActual, 101) + " 07:59:59 AM"))

IF @FechaActual >= @HoraComienzoTurno1 AND @FechaActual <= @HoraFinTurno1
BEGIN
    SELECT 1 AS Turno, @FechaActual AS FechaActual
END

IF @FechaActual >= @HoraComienzoTurno2 AND @FechaActual <= @HoraFinTurno2 
BEGIN
    SELECT 2 AS Turno, @FechaActual AS FechaActual
END

IF @FechaActual >= @HoraComienzoTurno3 OR @FechaActual <= @HoraFinTurno3 
BEGIN
    SELECT 3 AS Turno, dateadd(dd, -1, @FechaActual) AS FechaActual
END

EDIT Added "mi" to datepart() pattern, to manage also minutes