Examples for validating dates in pl sql

The return value has the same hours, minutes, and seconds component as the argument date.

SELECT TO_CHAR ( NEXT_DAY ( LAST_DAY ( ADD_MONTHS (TRUNC(SYSDATE,'Y'), ROWNUM-1))-7, TO_CHAR (TO_DATE('', 'DD-MM-YYYY'),'DAY') ), 'DD. YYYY') "Last Saturdays in 2004" FROM ALL_OBJECTS WHERE ROWNUM The "" is just a random date that we knew was a Saturday—any Saturday would do.

The compatible= parameter // must also be set.

CREATE TABLE t AS SELECT CAST(TIME'.000000' AS DATE) a FROM dual; CREATE TABLE t AS SELECT CAST(TIME'.000000' AS DATE) a FROM dual * ERROR at line 1: ORA-00932: inconsistent datatypes: expected NUMBER got TIME Advanced Oracle SQL Tuning training, Practical Linux Performance & Application Troubleshooting training (new), Advanced Oracle Troubleshooting training.

As you can see, there is a fractional seconds part of '.000000' on the end of this conversion.

This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). YYYY: HH24: MI: SS') ); COMMIT; SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table; Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype.

A new little feature in Oracle Database 12.2 is, that you can convert strings to dates without worrying about exception handling.The minimum number of letters required is the number of letters in the abbreviated version.Any characters immediately following the valid abbreviation are ignored.You can use this syntax also in some other situations, like the FLASHBACK TO TIMESTAMP and SELECT …AS OF TIMESTAMP clauses of a statement: Note the double “TIMESTAMP TIMESTAMP …” above.

Leave a Reply