2 Replies Latest reply: Apr 23, 2013 9:25 AM by Dick Thornburg RSS

    Help with Dates

    Dick Thornburg

      I am trying to load a date value in a script based on whether one date value is null or not. The one date value is in a delimiter string. It is loaded with the following command in the script.

       

      subfield("char_fld",';',5) AS so_user_Promise_date;

       

      The other date field is always populate. It is loaded int he script as follows:

       

      "ship_date" AS so_ship_date,

       

      I the use the following load function to create a date field with either so_user_Promise_date or so_ship_date:

       

      Load *,

      schedule_qty*so_unit_price AS so_total,

      so_open_qty*so_unit_price AS so_total_open,

      If(Len(Trim([so_user_Promise_date]))=0,so_ship_date,so_user_Promise_date) AS so_promise_date

      RESIDENT sols;

      drop table sols;

       

      The issue is so_promise_date display correctly if so_user_Promise_date is loaded. If so_ship_date is loaded I get the numeric value of the date. Example 41390. I have tried MakeDate, Date# with no luck. How can I get so_promise_date to always display the date?

        • Re: Help with Dates
          Gysbert Wassenaar

          so_ship_date is a correct date, so_user_Promise_date is a string. You should use the date# function on so_user_Promise_date to make a date of it. Once you've done that you can format so_promise_date in charts so it shows in a date format. If you want you can also do that in the script

           

          date(If(Len(Trim([so_user_Promise_date]))=0,so_ship_date,date#(so_user_Promise_date,'DD-MM-YYYY')),'DD-MM-YYYY') AS so_promise_date

           

          change the first DD-MM-YYYY with the date format that the string field so_user_Promise_date has. Change the second to however you want your dates displayed.