1 2 Previous Next 16 Replies Latest reply on Aug 16, 2017 11:43 AM by user4782111 Go to original post
      • 15. Re: Query help to convert varchar to date
        BluShadow

        user4782111 wrote:

         

         

        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

         

         

         

         

        What are those dates?

         

        2151223?

         

        Is that the number of seconds since a pre-defined date? or the number of minutes? or number of hours?

        Or is it some other format?

        Which part of it is the year? the month? the day? etc.?

         

        To convert it to a date, would require knowing what that data actually represents in the first place.

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

          Thank you all for your valuable input. I completely agree of really poor design of this table, esp date columns. At least, I see two issues now; two of the data input fields (PKT_DATE & DWNLD_DATE) look crap, the input values look incorrect.

          However, Cormaco's solution worked. This query now returns what I'm expecting:

          SELECT

          TO_DATE(SUBSTR(LAST_ACT_UPDATE,1,10),'MM/DD/YYYY')

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

          ,ACT_ID

          ,ACT_STAT

          ,ACT_NAME

          FROM ACT_MKTSTAT

           

          I'm able to see this resuts:

          LAST_ACT_UPDATE                PKT_DATE                 ACT_ID                ACT_STAT                ACT_NAME

          12-MAR-13                                10-APR-14                     0011                     TBL                         CTR GLAKES

          12-MAR-13                                10-APR-14                     0151                     YES                          JAL DNR

           

          These two columns (PKT_DATE & DWNLD_DATE) I'll have to work internally with DBA.

           

          Thanks all for your valuable input and time to resolve my issue.

          1 2 Previous Next