CODEWITHSUNDEEP
sqlsql-server
Jake Carroll
Jake Carroll

Reputation: 3

Between two dates and between two times

I am trying to get orders from between two dates and where those orders were between 5PM and 6:20 PM.

SELECT CREATION_DATE_TIME_STAMP
FROM SHIPMENT_HEADER 
WHERE CREATION_DATE_TIME_STAMP  between '2019-08-01 17:00:00:000'  and '2019-10-31 18:20:000'  AND
      ORDER_TYPE = 'Catalog'

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You need separate conditions on the date and time components. Date and time functions are notoriously database specific, but something like this:

SELECT CREATION_DATE_TIME_STAMP
FROM SHIPMENT_HEADER 
WHERE CREATION_DATE_TIME_STAMP >= '2019-08-01' AND
      CREATION_DATE_TIME_STAMP < '2019-11-01' AND
      CAST(CREATION_DATE_TIME_STAMP as TIME) >= '17:00:00' AND
      CAST(CREATION_DATE_TIME_STAMP as TIME) <= '18:20:00' AND
      ORDER_TYPE = 'Catalog';

This assumes that a simple cast() is sufficient to extract the time.

Upvotes: 2

Related Questions