Here's an example that shows what is probably happening to you:

DECLARE @ProductionShift TABLE

(

StartTime VARCHAR(10),

EndTime VARCHAR(10)

);

DECLARE @date DATETIME = '2013-09-09 02:30'

INSERT INTO @ProductionShift

(StartTime, EndTime)

VALUES

('22:00', -- StartTime - varchar(10)

'05:59' -- EndTime - varchar(10)

);

WITH dates

AS (

SELECT TOP (1)

CAST(REPLACE(CONVERT(VARCHAR, @date, 110), '/', '-') + ' ' + StartTime + ':00.000' AS TIME(5)) AS StartTime,

CAST(REPLACE(CONVERT(VARCHAR, @date, 110), '/', '-') + ' ' + EndTime + ':00.000' AS TIME(5)) AS EndTime,

CAST(@date AS TIME(5)) AS theDate

FROM

@ProductionShift

)

SELECT

*,

CASE WHEN dates.theDate BETWEEN dates.StartTime AND dates.EndTime THEN 1

ELSE 0

END AS isBetween

FROM

dates

Here's one way to get it to work:

DECLARE @ProductionShift TABLE

(

StartTime VARCHAR(10),

EndTime VARCHAR(10)

);

DECLARE @date DATETIME = '2013-09-09 02:30'

INSERT INTO @ProductionShift

(StartTime, EndTime)

VALUES

('22:00', -- StartTime - varchar(10)

'05:59' -- EndTime - varchar(10)

);

WITH dates

AS (

SELECT TOP (1)

CAST(REPLACE(CONVERT(VARCHAR, @date, 110), '/', '-') + ' ' + StartTime + ':00.000' AS DATETIME) AS StartTime,

CAST(REPLACE(CONVERT(VARCHAR, @date, 110), '/', '-') + ' ' + EndTime + ':00.000' AS DATETIME) AS EndTime,

CAST(@date AS TIME(5)) AS theDate

FROM

@ProductionShift

),

correctedDates

AS (

SELECT

*,

CASE WHEN startTime > EndTime THEN DATEADD(DAY, -1, STArtTIme)

ELSE STARTTIME

END AS newStartTIme

FROM

dates

)

SELECT

*

FROM

correctedDates

WHERE

@date BETWEEN correctedDates.newStartTIme

AND correctedDates.EndTime