To_char and To_date

techonthenet to_date
oracle to_char j
to_char(sysdate-1)
to_char oracle techonthenet
tech on the net to_date
oracle to_date format mask
oracle to_date 24 hour
to_char in java

I'm struggling with one report contain a varchar2 column as a date in MON-YY format

what I wanna do is convert the value of that column into date so I can do some process on it such as filtering with > or < or even between and set two dates

My column name: BAL.PERIOD_NAME below is the condition cussing error ORA-01843: not a valid month

AND TO_DATE(BAL.PERIOD_NAME,'MON-YY')  > TO_DATE('FEB-18','MON-YY')

thanks in advance

Make sure you are using the correct format mask for the date you are converting.

Works:

select  TO_DATE('feb-18','MON-YY') from dual

Throws "not a valid month" error:

select  TO_DATE('02-18','MON-YY') from dual

Difference between to_char and to_date., to_char function is used to convert the given data into character. SQL> SELECT TO_CHAR(SYSDATE, 'dd/mm/yyyy') FROM dual;  to_date(:1, 'yyyy-mm-dd') to_date(:2, 'hh24:mi:ss') to_date(:3, 'yyyy-mm-dd hh24:mi:ss') The user-defined SQL statements in Dynamic RDBMS stage also use TO_CHAR and TO_DATE SQL functions for Date, Time and Timestamp columns.

You can use a recursive CTE to check you table for invalif month names.

WITH cte(month)
AS
(
SELECT 1 month
       FROM dual
UNION ALL
SELECT month + 1 month
       FROM cte
       WHERE month + 1 <= 12
)
SELECT *
       FROM bal
       WHERE substr(period_name, 1, 3) NOT IN (SELECT to_char(to_date(month, 'MM'), 'MON')
                                                      FROM cte);

TO_CHAR - Convert Datetime to String, Query 1 : select to_char(sysdate,'DD-MON-YYYY') AS REQ_DATE from dual; To_date converts a STRING into a date using the format mask. TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.

TO_DATE - Convert String to Datetime, on a date (like you have done) causes the date to be implicitly converted to a string and that string gets converted back to a date using your format mask - it is plain wrong. The Oracle TO_CHAR () accepts three arguments: The expr is a DATE or an INTERVAL value that should be converted. The data type of expr can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE. The date_format is a string that determines the format that the result string should be in.

SQL - Conversion Functions, function converts a datetime value (DATE, TIMESTAMP data types i.e.) to a string using the specified format. In SQL Server, you can use CONVERT or CAST functions to convert a datetime value (DATETIME, DATETIME2 data types i.e.) to a string. to_char and to_date functions will implicitly use the NLS_DATE_FORMAT setting for the current SESSION, if the format string is not given. Because we developers cannot control in every case what this will be for all users, countries and applications it is usually sensible to provide the date format when we are manipulating and displaying dates.

Server, you can use CONVERT or TRY_CONVERT function with an appropriate datetime style. TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of VARCHAR2 data type in the format specified by the date format fmt. If you omit fmt, then date is converted to a VARCHAR2 value as follows:

to convert the hire date to the date format MONTH DD, YYYY i.e. month spelled out and padded with spaces, followed by the two-digit day of the month, and then the four-digit year. I want to convert the following format to date format(DD-MON-YYYY) BUT am unable to do so. I have tried the following queries:- SELECT to_date(TO_CHAR (termination_date, 'DD/MON/RRRR HH24:MI:SS'

Comments
  • You probably don't have a legit MON (month abbreviation) in your BAL.PERIOD_NAME for one of your records and so it is unable to convert to date. Perhaps it has something to do with your NLS_DATE_LANGUAGE? or you just have bad data. Also TO_DATE('FEB-18', 'MON-YY') is an expensive way to say DATE '2018-02-01'