0

Could use fresh POV help :)

Need to show trips between midnight and 4AM. StartTime column is of type DATETIME. I already tried 

CAST(StartTime AS TIME) BETWEEN CAST('00:00:00' AS TIME) AND....

but it didn't show the correct results and so I've done this IIF but still even though the StartTime is NOT between midnight and 4AM, the results shows 1. Am I missing something? Thank for your help! 

SELECT 
    id
,   StartTime
,   DATEADD(d, 0, DATEDIFF(d, 0, StartTime)) AS Midnight
,   DATEADD(hh, +4 , DATEDIFF(d, 0, StartTime)) AS FourAM
,   IIF((StartTime BETWEEN DATEADD(d, 0, DATEDIFF(d, 0, StartTime))
                      AND DATEADD(hh, +4 , DATEDIFF(d, 0, StartTime))), 1, 0) _Between_Check
FROM 
    FUN.Trip

Results:

ID          | StartDate             | Midnight                | FourAM                  | Between_Check
------------+-----------------------+-------------------------+-------------------------+--------------
-2135024021 | 19-10-02 00:04:01.000 | 2019-10-02 00:00:00.000 | 2019-10-02 00:04:00.000 | 1
-2135024228 | 19-10-05 00:04:30.000 | 2019-10-05 00:00:00.000 | 2019-10-05 00:04:00.000 | 1

Above is the result I'm getting. The Between_Check column should be showing 0 as the StartDate is a minute past 4AM

This is what I should be getting

Results:

ID          | StartDate             | Midnight                | FourAM                  | Between_Check
------------+-----------------------+-------------------------+-------------------------+--------------
-2135024021 | 19-10-02 00:04:01.000 | 2019-10-02 00:00:00.000 | 2019-10-02 00:04:00.000 | 0
-2135024228 | 19-10-05 00:02:30.000 | 2019-10-05 00:00:00.000 | 2019-10-05 00:04:00.000 | 0
TommyD
  • 15
  • 4
  • please show some sample data and expected result in text and not image – Squirrel Nov 11 '19 at 01:52
  • Thanks and sorry for formatting. I'm new to stackoverflow so need to gain experience in it. ' id | StartTime | Midnight -2135024021 | 2019-10-02 00:04:01.000 | 2019-10-02 00:00:00.000 | FiveAM | BETWEEN_Check 2019-10-02 05:04:01.000 | 1 -2135024020 | 2019-10-01 05:27:25.000 | 2019-10-01 00:00:00.000 | 2019-10-01 10:27:25.000 |1 -2135024020 | 2019-10-01 05:58:43.000 |2019-10-01 00:00:00.000 | 2019-10-01 10:58:43.000 |1 ' Two last rows should be showing 0 as the Time is beyond 5AM. Each row starts with -213502 – TommyD Nov 11 '19 at 02:00
  • please update your question with these information and not in comment – Squirrel Nov 11 '19 at 02:14
  • Curious thing that `DATEADD(hh, +4 , DATEDIFF(d, 0, StartTime)) AS FourAM` results in `2019-10-02 00:04:00.000`. Your system must be adding metric hours that are only one Imperial minute each. – HABO Nov 11 '19 at 03:33
  • Thanks HABO. Did you mean it should be 04:00:00 rather than 00:04:00 – TommyD Nov 11 '19 at 04:55
  • @TommyD It depends. Most of us use `hh:mm:ss` for times. Do you use `ss:hh:mm`? – HABO Nov 15 '19 at 13:14

2 Answers2

0

if you want to check if the time is between midnight and 4 AM, why not simply use datepart(hour, {datetime})

IIF (DATEPART(HOUR, StartTime) BETWEEN 0 AND 4, 1, 0)

or alternatively, CONVERT() the StartTime to time data type and then compare with time string

IIF (CONVERT(TIME, StartTime) BETWEEN '00:00' AND '04:00', 1, 0)

or

IIF (CONVERT(TIME, StartTime) >= '00:00' AND CONVERT(TIME, StartTime) < '04:00', 1, 0)

Not sure you wanted the time 04:00 to be inclusive or not. Anyway above are various option you can use

what you are doing here will always be true

IIF(
        (StartTime BETWEEN 
                                DATEADD(d, 0, StartTime) 
                            AND DATEADD(hh, +4, StartTime))
        , 1, 0)

because DATEADD(d, 0, StartTime) has basically no effect and is equal to StartTime

Squirrel
  • 23,507
  • 4
  • 34
  • 32
  • Thanks Squirrel! This still results in 1 in the above shown case. The StartTime is 1 minute past 4. Your idea, thanks! does work with GETDATE() as StartTime but doesn't work with the actual StartTime, example with data I've pasted in the question. Thanks for your time Squirrel. – TommyD Nov 11 '19 at 02:54
  • I get you, I've changed it to `DATEADD(hh, +4 , DATEDIFF(d, 0, StartTime))` but still I'm getting the same result. Bugs me completely. – TommyD Nov 11 '19 at 03:01
  • please update the sample data in the question. It is unreadable in the comments – Squirrel Nov 11 '19 at 03:08
  • if the logic required does not include `04:00` then change to `BETWEEN 0 AND 3` – Squirrel Nov 11 '19 at 03:09
  • Hi Squirrl, thaks mate for your time with this. I've edited the question. See results :) Hope it makes sense now – TommyD Nov 11 '19 at 04:49
0

The answer is in the HABO comment who made me look at the result set.

TommyD
  • 15
  • 4