Oracle query BETWEEN (date) AND (date)

Question

My table "Message" contain a column name : message_date (datatype : TIMESTAMP) which stores date and time. But in this case, I would like to only show the date of the data, so I use the method to_char(case(message_date as date),'DD-MM-YYYY')

SELECT msg_id, msg_details, to_char(cast(message_date as date) ,'DD-MM-YYYY')as "DATE"
FROM message
WHERE message LIKE '%hi%'
AND to_char(cast(message_date as date), 'DD-MM-YYYY')
BETWEEN '15-01-2018'
AND '30-01-2018'

I would like to show only the row between the date 15-01-2018 and 30-01-2018. But in the end, the query result came out with the data which from date 15 - 30..where row with other month (exp: 20-03-2018 also shown in the result. I not sure why it only check for the date and not together with month and year... Seek for help ..thanks

Answer 1

Use date literals with the dates in an ISO compliant format:

WHERE
    message LIKE '%hi%' AND
    message_date BETWEEN date '2018-01-15' AND date '2018-01-30'

Notr that you don't need to cast message_date to text, because it is already a timestamp and can be directly compared to dates.

Answer 2

The only issue with using BETWEEN when comparing dates is if one or more of the dates you're comparing has a time portion. For example, the date 30-JAN-18 01.32.32 PM certainly isn't between 15-JAN-18 and 30-JAN-18 - it's greater (er, later) than the latter date. Plus while using BETWEEN, which is inclusive, there is always the chance of including an edge case you didn't intend. My recommendation would be to do something like this:

SELECT msg_id, msg_details, TO_CHAR(message_date, 'DD-MM-YYYY') AS "DATE"
  FROM message
 WHERE message LIKE '%hi%'
   AND message_date >= DATE'2018-01-15'
   AND message_date < DATE'2018-01-30' + 1;

Notice in the first line I got rid of your CAST() - there is no reason to cast a date value to the DATE datatype. In the last line I'm using a bit of Oracle date arithmetic; this will give me all dates up to and including 30-JAN-18 11:59.59.9999.....

As an aside you might want to apply the LOWER() function to message in your WHERE clause:

 WHERE LOWER(message) LIKE '%hi%'

otherwise you will miss messages containing Hi, HI, hI, etc.