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

Code Examples Control
#1

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_type,tr_not_balanced)
VALUES (’{$_date}’,{$_amount},’{$_payee}’,{$_drcr},{$_bank},{$_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.date-default-timezone-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… :frowning:

#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’];