Is there an easy way to convert 12-Jan-16 to db_format?

Scriptcase 8 Discussion
#1

Hi everyone,

I have a situation where I import the contents of a csv file that includes a date field.

The format of the date field can be variable as I have no real control over its creation.

I have used the sc_date_conv macro for date fields like 12/12/15 and 12-12-15 and 12.12.15 but now I have come across a csv file where the date is recorded in the format
12-Jan-15.

I do not think that sc_date_conv can handle alpha characters, and I suppose I could do some string manipulation using some switch/case code to remove the alpha and replace it with numeric before the sc_date_conv does its work.

However, if there is an easier way, I am all ears.

Thanks

Tony

#2

Here is what I would do,
Open the csv in excel, convert the dates and resave to csv or import directly into the database from excel

My 2 cents

#3

Thanks KDriscoll.

The particular application here, uploads a csv file of bank statement information, generated by a number of different banks. Not all my users would have access to Excel so I whilst I might be able to offer the option of uploading an Excel file, the user may still not have the skills to convert to a more appropriate format.

Thanks for your thoughts.

Tony

#4

This may be of help

$findate = DateTime::createFromFormat(‘d-M-y’, ‘12-Jan-15’);
{t} = $findate->format(‘Y-m-d’);

#5

Thanks William,

I have had some success, but there is still something I am missing. Here is my code around this area -

First I am checking for the delimiter - in this case the - character.

if ($date_check[1] == '-'){

Then I check to see if this is a single character start eg. 1st - 9th of the month and add a zero to the front of the date.

$date_check = '0'.$date_check;

}

Then I added your code and using the litteral string ‘12-Jan-16’ it works a treat and the conversion works well later in the code.

$findate = DateTime::createFromFormat('d-M-y', '12-Jan-15');

{t} = $findate->format('d-m-Y');


$_date = sc_date_conv({t},"dd-mm-yyyy","db_format");

However, if I try to use the date field variable $date_check instead of the litteral string ‘12-Jan-16’ then it fails, and an error is received

Fatal error: Call to a member function format() on a non-object

So, I am not sure what to do next? Any ideas what I need to do?

Thanks

Tony

#6

Hi

You do not need to check for leading zeros etc the createFromFormat function with the d parameter takes care of that
Also you should not need to reconvert to db_format as the 2nd line will produce date in the format dd-mm-yyyy

$date_check = ‘1-Feb-15’;
$findate = DateTime::createFromFormat(‘d-M-y’, $date_check);

echo $findate->format(‘d-m-Y’);

#7

Thanks William.

Your method worked for most situations but did cause me grief if the date delimiter was a / chr.

So here is my final code that works for these date formats (only interested in Australia and UK at this time) and does numeric and alpha months.

dd/mm/yyyy
dd-mm-yyyy
dd.mm.yyyy
dd/M/yyyy
dd-M-yyyy
dd.M.yyyy

// turn dates with / delimiter to a delimiter of -
$date_check = str_ireplace("/","-",$date_check);
$timestamp = strtotime($date_check);
$date_check = date(‘d-m-Y’,$timestamp );
$_date = sc_date_conv($date_check,“dd-mm-yyyy”,“db_format”);

All now displays and saved in the desired formats.

Tony