Menu
If I have table with a Date column called myDate, with values like 2009-08-25 09:00:09.0.
I want to select all rows for Aug 25, from 12:00:01 AM until 11:59:59 PM and NOTHING for Aug 26. Is it sufficient to simply use the condition:
where myDate between Date '2009-08-25' and Date '2009-08-26'
And I want to select all rows BEFORE Aug 25, not including Aug 25. Can I do:
where myDate < Date '2009-08-25'
519
Generally it's a good idea to post your 4-digit Oracle version and o/s information. Have a look at the Oracle Built-in Data Types in the documentation. You will find there that the "Valid date range from January 1, 4712 BC, to December 31, 9999 AD." in Oracle.
Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.
if you want data for the full day 25 and excluding all the 26, you need to remove the first second of the 26:
where myDate >= Date '2009-08-25' and myDate < Date '2009-08-26'
or
where myDate between Date '2009-08-25' and Date '2009-08-26' - interval '1' second
Update -- A little precision: In Oracle the DATE datatype is used both for 'Date' types with or without time. If you're dealing with dates without time, it is usually stored with a 00:00 time segment, meaning midnight. Because of this, the first between (my_date between '2009-08-25' and '2009-08-26') will potentially select two full days.
By removing the first second of the 26, you ensure that you won't inadvertently select rows from the 26.
80
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
