1 2 Previous Next 16 Replies Latest reply on Aug 16, 2017 11:43 AM by user4782111

    Query help to convert varchar to date

    user4782111

      Hello,

      I'm trying to run the following query:

      SELECT

      LAST_ACT_UPDATE

      ,PKT_DATE

      ,DWNLD_DATE

      ,PROCESS_DATE

      ,ACT_ID

      ,ACT_STAT

      ,ACT_NAME

      FROM ACT_MKTSTAT

       

      Here is how the data displays:

      LAST_ACT_UPDATE           PKT_DATE           DWNLD_DATE           PROCESS_DATE           ACT_ID           ACT_STAT           ACT_NAME

      03/12/2013 0:00:00                2151203                2151223                     10-APR-14                     0011                TBL                     CTR GLAKES

      11/09/2015 0:00:00                2150826                    2151223                  10-APR-14                     0151                YES                    JAL DNR

       

      The ACT_MKTSTAT table has about 500 records. Below are the column types:

       

      LAST_ACT_UPDATE                    VARCHAR2(30)

      ,PKT_DATE                                   VARCHAR2(30)

      ,DWNLD_DATE                             VARCHAR2(30)

      ,PROCESS_DATE                         DATE

      ,ACT_ID                                         NUMBER

      ,ACT_STAT                                    VARCHAR2(20)

      ,ACT_NAME                                  VARCHAR2(10)

       

      The NLS_DATE_FORMAT of our DB shows as "DD-MON-RR".

       

      I'm trying to display the data (only date part) in this format:

       

       

      LAST_ACT_UPDATE                     PKT_DATE                     DWNLD_DATE                PROCESS_DATE                ACT_ID                ACT_STAT                ACT_NAME

      12-MAR-2013                               DD-MON-YYYY                DD-MON-YYYY                10-APR-14                            0011                     TBL                          CTR GLAKES

      12-MAR-2013                               DD-MON-YYYY                DD-MON-YYYY                10-APR-14                            0151                     YES                          JAL DNR

       

      I ran the following SELECT to accomplish the above results but keep getting Oracle error (ORA-01843: not a valid month):

       

      SELECT

      TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY')

      ,TO_DATE(PKT_DATE, 'DD-MON-YY')

      ,TO_DATE(DWNLD_DATE, 'DD-MON-YY')

      ,PROCESS_DATE  --no need to convert it as it's in Date type

      ,ACT_ID

      ,ACT_STAT

      ,ACT_NAME

      FROM ACT_MKTSTAT

       

      I also tried to convert it to the following format but no success:

       

      'DD/MM/YYYY' and 'MM/DD/YYYY' and 'YYYY/MM/DD' and 'YYYY/DD/MM' and 'DD-MM-YY' and 'MM-DD-YY' and 'DD-MON-YY' and

      'DD-MON-YYYY HH24:MI' and 'yyyy/mm/dd' and 'DD-MON-YYYY HH24:MI'.

       

      What I'm doing wrong? All I want to do is to show the date fields in "DD-MON-YYYY" format, i.e. 01-JAN-2017.

       

      Any help would be great.

       

       

      Thanks,

        • 1. Re: Query help to convert varchar to date
          John Thorton

          SELECT

          TO_CHAR(TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY'),'DD-MON-YYYY')

          ,TO_CHAR(TO_DATE(PKT_DATE, 'DD-MON-YY'),'DD-MON-YYYY')

          ,TO_CHAR(TO_DATE(DWNLD_DATE, 'DD-MON-YY'),'DD-MON-YYYY')

          ,TO_CHAR(PROCESS_DATE,'DD-MON-YYYY')

          ,ACT_ID

          ,ACT_STAT

          ,ACT_NAME

          FROM ACT_MKTSTAT;

          • 2. Re: Query help to convert varchar to date
            user4782111

            Thanks for your quick reply John!

             

            I still get the same error message:

             

            ORA-01843: not a valid month

            01843. 00000 -  "not a valid month"

            *Cause:   

            *Action:

            • 3. Re: Query help to convert varchar to date
              John Thorton

              user4782111 wrote:

               

              Thanks for your quick reply John!

               

              I still get the same error message:

               

              ORA-01843: not a valid month

              01843. 00000 - "not a valid month"

              *Cause:

              *Action:

              Which line/column throws the error?

              It means that the content of one of the columns that is VARCHAR2 that supposedly contains a "date"  does NOT conform to the format you specified.

              You need to identify what the bad data is in your table.

               

              SELECT

              TO_CHAR(TO_DATE(LAST_ACT_UPDATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MON-YYYY')

              ,TO_CHAR(TO_DATE(PKT_DATE, 'DD-MON-YY'),'DD-MON-YYYY')

              ,TO_CHAR(TO_DATE(DWNLD_DATE, 'DD-MON-YY'),'DD-MON-YYYY')

              ,TO_CHAR(PROCESS_DATE,'DD-MON-YYYY')

              ,ACT_ID

              ,ACT_STAT

              ,ACT_NAME

              FROM ACT_MKTSTAT;

              • 4. Re: Query help to convert varchar to date
                mNem

                Here is how the data displays:

                LAST_ACT_UPDATE           PKT_DATE           DWNLD_DATE           PROCESS_DATE           ACT_ID           ACT_STAT           ACT_NAME

                03/12/2013 0:00:00                2151203                2151223                     10-APR-14                     0011                TBL                     CTR GLAKES

                11/09/2015 0:00:00                2150826                    2151223                  10-APR-14                     0151                YES                    JAL DNR

                 

                ...

                 

                LAST_ACT_UPDATE                     PKT_DATE                     DWNLD_DATE                PROCESS_DATE                ACT_ID                ACT_STAT                ACT_NAME

                12-MAR-2013                               DD-MON-YYYY                DD-MON-YYYY                10-APR-14                            0011                     TBL                          CTR GLAKES

                12-MAR-2013                               DD-MON-YYYY                DD-MON-YYYY                10-APR-14                            0151                     YES                          JAL DNR

                 

                I ran the following SELECT to accomplish the above results but keep getting Oracle error (ORA-01843: not a valid month):

                 

                SELECT

                TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY')

                ,TO_DATE(PKT_DATE, 'DD-MON-YY')

                ,TO_DATE(DWNLD_DATE, 'DD-MON-YY')

                ,PROCESS_DATE  --no need to convert it as it's in Date type

                ,ACT_ID

                ,ACT_STAT

                ,ACT_NAME

                FROM ACT_MKTSTAT

                 

                Check the values highlighted in red. you are trying to call something like to_date('2151203', 'DD-MON-YY')

                • 5. Re: Query help to convert varchar to date
                  user4782111

                  mNem,

                  As per your suggestion, I pretty much commented out everything except LAST_ACT_UPDATE column which has data in 'MM/DD/YYYY HH:MI:SS" format. So my new query looks like this (Please also see the sample data I provided in this discussion):

                  SELECT

                  LAST_ACT_UPDATE        --sample data format (it's stored as varchar2) "03/12/2013 0:00:00" and "11/09/2015 0:00:00" should return for this query

                  ,TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY')  --Just want to confirm if it's really working.

                  FROM ACT_MKTSTAT

                  WHERE ACT_ID IN ('0011','0151')

                   

                  and this is what I get:

                   

                  ORA-01843: not a valid month

                  01843. 00000 -  "not a valid month"

                  *Cause:   

                  *Action:

                  • 6. Re: Query help to convert varchar to date
                    John Thorton

                    SELECT

                    TO_CHAR(TO_DATE(LAST_ACT_UPDATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MON-YYYY')

                    ,ACT_ID

                    ,ACT_STAT

                    ,ACT_NAME

                    FROM ACT_MKTSTAT;

                    • 7. Re: Query help to convert varchar to date

                      Post a SIMPLE EXAMPLE of data showing:

                       

                      1. what the date data looks like in the table

                      2. how it should look as a string

                      • 8. Re: Query help to convert varchar to date
                        John Thorton

                        This thread is a CLASSIC example of why it is bad, lazy, foolish & unprofessional to try to store a "date" in any column with datatype other than DATE

                        • 9. Re: Query help to convert varchar to date
                          cormaco

                          Hi there,

                           

                          ,TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY') --Just want to confirm if it's really working.

                          this is not working because the format string in TO_DATE must match the input data, in your example  "03/12/2013 0:00:00"

                          Try this:

                           

                          SELECT TO_DATE(SUBSTR('03/12/2013 0:00:00',1,10),'MM/DD/YYYY') FROM dual

                          • 10. Re: Query help to convert varchar to date
                            Paul  Horth

                            user4782111 wrote:

                             

                            Hello,

                            I'm trying to run the following query:

                            SELECT

                            LAST_ACT_UPDATE

                            ,PKT_DATE

                            ,DWNLD_DATE

                            ,PROCESS_DATE

                            ,ACT_ID

                            ,ACT_STAT

                            ,ACT_NAME

                            FROM ACT_MKTSTAT

                             

                            Here is how the data displays:

                            LAST_ACT_UPDATE PKT_DATE DWNLD_DATE PROCESS_DATE ACT_ID ACT_STAT ACT_NAME

                            03/12/2013 0:00:00 2151203 2151223 10-APR-14 0011 TBL CTR GLAKES

                            11/09/2015 0:00:00 2150826 2151223 10-APR-14 0151 YES JAL DNR

                             

                            The ACT_MKTSTAT table has about 500 records. Below are the column types:

                             

                            LAST_ACT_UPDATE VARCHAR2(30)

                            ,PKT_DATE VARCHAR2(30)

                            ,DWNLD_DATE VARCHAR2(30)

                            ,PROCESS_DATE DATE

                            ,ACT_ID NUMBER

                            ,ACT_STAT VARCHAR2(20)

                            ,ACT_NAME VARCHAR2(10)

                             

                            The NLS_DATE_FORMAT of our DB shows as "DD-MON-RR".

                             

                            I'm trying to display the data (only date part) in this format:

                             

                             

                            LAST_ACT_UPDATE PKT_DATE DWNLD_DATE PROCESS_DATE ACT_ID ACT_STAT ACT_NAME

                            12-MAR-2013 DD-MON-YYYY DD-MON-YYYY 10-APR-14 0011 TBL CTR GLAKES

                            12-MAR-2013 DD-MON-YYYY DD-MON-YYYY 10-APR-14 0151 YES JAL DNR

                             

                            I ran the following SELECT to accomplish the above results but keep getting Oracle error (ORA-01843: not a valid month):

                             

                            SELECT

                            TO_DATE(LAST_ACT_UPDATE,'DD-MON-YY')

                            ,TO_DATE(PKT_DATE, 'DD-MON-YY')

                            ,TO_DATE(DWNLD_DATE, 'DD-MON-YY')

                            ,PROCESS_DATE --no need to convert it as it's in Date type

                            ,ACT_ID

                            ,ACT_STAT

                            ,ACT_NAME

                            FROM ACT_MKTSTAT

                             

                            I also tried to convert it to the following format but no success:

                             

                            'DD/MM/YYYY' and 'MM/DD/YYYY' and 'YYYY/MM/DD' and 'YYYY/DD/MM' and 'DD-MM-YY' and 'MM-DD-YY' and 'DD-MON-YY' and

                            'DD-MON-YYYY HH24:MI' and 'yyyy/mm/dd' and 'DD-MON-YYYY HH24:MI'.

                             

                            What I'm doing wrong? All I want to do is to show the date fields in "DD-MON-YYYY" format, i.e. 01-JAN-2017.

                             

                            Any help would be great.

                             

                             

                            Thanks,

                            Whoever "designed" that table needs "re-education" (with a large stick).

                            Make noises at work to get the table designed properly and hold dates in DATE columns.

                            • 11. Re: Query help to convert varchar to date
                              Cookiemonster76

                              Before you go any further you should read this: PL/SQL 101 : DataTypes - DATE

                               

                              Bottom line though - when you're to_dating a string you need to supply a format mask that matches the string. Cormaco showed you one way, though I would supply the full mask rather than use substr:

                              SELECT TO_DATE('03/12/2013 0:00:00','MM/DD/YYYY HH24:MI:SS') FROM dual;

                               

                              That's not going to help with pkt_date and dwnld_date - they don't appear to be in any recognisable format. What date is 2151203?

                              • 12. Re: Query help to convert varchar to date
                                Nimish Garg

                                You should first check if your "DATE" in varchar2 fields are actually dates, and fix/avoid the illegal dates. You can use following function (add your date format in to_date)

                                 

                                CREATE OR REPLACE FUNCTION F_DATE(v_date IN VARCHAR2) RETURN NUMBER IS
                                    v_date1 DATE;
                                BEGIN
                                    select to_date(v_date) into v_date1 from dual;
                                        RETURN 1;
                                    Exception WHEN Others THEN
                                        RETURN 0;
                                END;
                                ----------------------------------------------   
                                SELECT F_DATE('01-JAN-09') FROM DUAL;
                                -- Returns 1
                                SELECT F_DATE('111111') FROM DUAL;
                                -- Returns 0

                                 

                                how to check for valid date ?

                                • 13. Re: Query help to convert varchar to date
                                  Cookiemonster76

                                  That function needs a format mask - probably as a parameter.

                                  None of the dates are in the DBs nls_date_format (you could change it at session level admittedly) and the different columns appear to have different formats.

                                  • 14. Re: Query help to convert varchar to date
                                    Paulzip

                                    user4782111 wrote:

                                     

                                    What I'm doing wrong?

                                     

                                    Any help would be great.

                                     

                                     

                                    Thanks,

                                    What are you doing wrong?  You're storing dates as strings.  FULL STOP.

                                     

                                    Store dates as dates, then you won't get these problems.

                                     

                                    In the meantime, fiddle with your query until all of the data is in date format, then fix your table based on that query.  Then suggest to the person who designed the table that he get some remedial training in good basic DB practices.

                                    1 2 Previous Next