Announcement

Collapse
No announcement yet.

sc_date_conv() not behaving itself - or am I the one at fault?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • sc_date_conv() not behaving itself - or am I the one at fault?

    I am reading in a csv file and one of the fields contains a date in the format dd/mm/yyyy, however, on the first nine days it is d/mm/yyyy.

    I have used sc_date_conv to try and code it to the db_format and the code works fine until those first 9 days of the month and then I get errors - the dates being added to the INSERT INTO string go haywire.

    Is this a bug in SC or should I be using a conditional statement based on the day of the month, or what?

    I have placed my code below, but to summarize 10/04/2015 works but 9/04/2015 does not.

    Thanks

    Tony

    Code under the "import" button.

    $cnt=0;
    $handle = fopen({csv_file}, 'r');
    if ($handle)
    {
    set_time_limit(0);

    //loop through one row at a time
    while (($data = fgetcsv($handle, 4096, ',')) !== FALSE)
    {

    $_date = sc_date_conv($data[0],"dd/mm/YYYY","db_format");
    $_amount = ABS($data[1]);
    $_payee = $data[2];
    if ($data[1] < 0) {
    $_drcr = 1;
    }else{
    $_drcr = -1;
    }
    $_bank = {bank_code};
    if ($data[1] < 0) {
    $_type = 1;
    }else{
    $_type = 0;
    }
    $_not_balanced = 1;

    $stm ="INSERT INTO transactions (tr_date,tr_amount,tr_payee,tr_drcr,tr_bank,tr_typ e,tr_not_balanced)
    VALUES ('{$_date}',{$_amount},'{$_payee}',{$_drcr},{$_ban k},{$_type},{$_not_balanced})";

    sc_exec_sql($stm);

    $cnt++;
    }

    fclose($handle);
    }

  • #2
    So you mean that sc_date_conv isnt working? If so then simply use something like strptime with %d/%m/%Y
    You are actually reading a csv file as text so assuming that the date is dd/mm/YYYY is not the best thing to do.
    Anyway see this: http://php.net/manual/en/function.da...mezone-set.php
    Posibly your default php date input format is not the same as the one in your csv file. So you should take notice of possible bad conversions.
    In fact when it is exported from an excel sheet it is quite possible that the date gets jumbled up depending on the language setting of the machine that created the csv file...

    Comment


    • #3
      Thanks rr,

      When I try to implement strptime I get undefined function error message for it. Am I supposed to do some form of Include statement somewhere?

      Thanks

      Tony

      Here is the replacement code I used

      $ret_date = strptime($data[0],'%d/%m/%Y');
      $_date1 = ($ret_date['tm_year']+1900)."-".$ret_date['tm_mon']."-".$ret_date['tm_mday'];
      Last edited by fiscal; 04-16-2015, 03:14 AM.

      Comment

      Working...
      X