Menu
In the Toad editor, I've got this SQL:
SELECT INTERLOPERABCID,AVAILABLEDATE,
AVAILABLEQHR,CARPHONEID,
TRUNC((AVAILABLEQHR-1)/12) "COL",
MOD(AVAILABLEQHR-1,12) "ROW"
FROM ABC.INTERLOPERAVAILABILITY
WHERE INTERLOPERABCID>42
AND AVAILABLEDATE='09/Apr/2012'
...but it returns no rows, even though I see many records in the table whose AVAILABLEDATE value = '4/9/2012'
The date format ('09/Apr/2012'; I also tried '04/Sep/2012') looks odd, but at least I get no compilation/execution error with it. When I tried "4/9/2012" and "04/09/2012" it said "ORA-01853: not a valid month"
How must I enter the date to tell it I want to see dates of April 9th, 2012?
324
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'; alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'; alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS'; select sysdate from dual; select systimestamp from dual; Session altered. Session altered.
Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY. SYSDATE is a function returning date and time.
SQL Date Data Types DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
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.
Safest to use TO_DATE:
AND AVAILABLEDATE = TO_DATE('09/Apr/2012', 'DD/Mon/YYYY')
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
