In MariaDB, for some date formats you can also use CAST AS DATETIME function without specifying the date format, for other format you have to use STR_TO_DATE function and specify the format explicitly: # ORA-01861: literal does not match format string # ORA-01861: literal does not match format string SELECT TO_DATE ( '20210915' ) FROM dual # ORA-01861: literal does not match format string SELECT TO_DATE ( '2021.09.15' ) FROM dual # ORA-01861: literal does not match format string SELECT TO_DATE ( '5' ) FROM dual Set default format to DD-MON-YYYY ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' # - Including YYYYMMDD format SELECT TO_DATE ( '20210915' ) FROM dual īut when another format is specified Oracle allows only values matching the format: # - But other delimiters are also recognized SELECT TO_DATE ( '5' ) FROM dual This format matches NLS_DATE_FORMAT SELECT TO_DATE ( '' ) FROM dual Set default format to YYYY-MM-DD ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' Note that if NLS_DATE_FORMAT is set to 'YYYY-MM-DD', 'YYYY/MM/DD' or 'YYYY.MM.DD' Oracle TO_DATE recognizes various delimiters: # DD-MON-RR - So we can convert '21-DEC-17' to DATE without specifying format SELECT TO_DATE ( '21-DEC-17' ) FROM dual ![]() ![]() Oracle: - Let's determine the current default DATE format: SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_DATE_FORMAT' If a string literal matches the default DATE format string defined in Oracle, you can use TO_DATE function to convert a string to DATE without specifying the format string:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |