date formatting again

baperl has asked for the wisdom of the Perl Monks concerning the following question:

I have the following sql code in my perl file to load time series data into my DB. Unfortunately, the date is formatted as 'dd/mm/yyyy'. ordinarily, I would have chomped the line, split the csv and then written the following

my $trade_dt=UnixDate($tdate,"%Y-%m-%d");
[download]

however, I am using the following code to load the data and couldn't get the UnixDate() to work...

LOAD DATA LOCAL INFILE '$fname' INTO TABLE $tname FIELDS TERMINATED BY
+ ',' (trade_dt,stock_id, volume);
[download]

how do I fix the date formatting so the data is inserted in yyyy-mm-dd format

Comment on date formatting again Select or Download Code

Replies are listed 'Best First'.
Re: date formatting again by mbethke (Hermit) on Jan 23, 2012 at 05:44 UTC
Well, if you're bypassing Perl by letting the database server do all the importing, it's no wonder you can't use Perl functions :) What about preprocessing the file? Read each line, split it up, convert the date as you would have otherwise and put it back together. That or insert the data manually using DBI, but preprocessing will likely be both easier and faster.	[reply]
Re: date formatting again by roboticus (Chancellor) on Jan 23, 2012 at 11:21 UTC
baperl: All databases that I've worked with have date parsing functions available in them already. So read your database documentation on how to format and parse dates, and let the database do the work. Secondly: You *are* storing the dates as dates and not strings, right? If you're using strings you may be making things harder for yourself down the road. Update: A little googling turned up: http://forums.mysql.com/read.php?10,136269,136273#msg-136273, http://stackoverflow.com/questions/2238611/how-to-change-string-date-to-mysql-date-format-at-time-of-import-of-csv-using-my. Does google not work over there? ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re^2: date formatting again by baperl (Sexton) on Jan 23, 2012 at 16:36 UTC
I am storing the dates as dates. I did look up the links, thanks for those, @roboticus however, when I run the code by adding @var into my LOAD statement, `LOAD DATA LOCAL INFILE '$fname' INTO TABLE $tname FIELDS TERMINATED BY + ',' LINES TERMINATED BY '\r\n' (trade_date,....,@var) SET trade_date + = str_to_date(@var, '%d/%m/%Y');` [download] I get the following error: `Global symbol "@var" requires explicit package name at process.pl line + 37. Execution of process.pl aborted due to compilation errors.` [download]	[reply] [d/l] [select]