I am trying to filter a database within time range. I wrote something like this below.

WHERE
    CAST(DateTimeColumn AS TIME) BETWEEN CAST(@StartTime AS TIME) 
                                     AND CAST(@EndTime AS TIME)

This works as it should, but the problem occurs when the parameters are converted from Time zone change. For example, I've set the default time as 12:00am to 11:59pm to cover 23:59 hours of range. When these parameters are changed to UTC time from Singapore time zone, it becomes 04:00pm to 03:59pm. Thus, covering -1 minute of time range or in code it becomes.

WHERE 
     CAST(DateTimeColumn AS TIME) BETWEEN 04:00pm AND 03:59pm

The code above will not return anything since it searched negative timeframe.

I have searched and learned that I can use NOT BETWEEN and CASE to accommodate all different Time zones. Is there an easier way to make this work without writing CASE statement for every possible time zones?

CodePudding user response：

Is this what you are looking for?

1. if @StartTime is less then @EndTime then check if your column time is between the @StartTime and @EndTime

2. if @StartTime is greater then @EndTime then check if your column time is greater than your @StartTime or less than your @EndTime.

(@StartTime < @EndTime AND CAST(DateTimeColumn AS TIME) BETWEEN @StartTime AND @EndTime)
OR (@StartTime > @EndTime AND (CAST(DateTimeColumn AS TIME) >= @StartTime OR CAST(DateTimeColumn AS TIME) <= @EndTime))