DBI:mysql date conversion question

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

I am using Spreadsheet::ParseExcel to read data from an excel spreadsheet. I have encountered a problem ingesting dates. The excel date format is mm/dd/yyyy whereas mysql expects yyyy-mm-dd. I tried reformating the cells in the spreadsheet and I can, in fact, format the presentation of the date to match the yyyy-mm-dd format that mysql expects, but when I select a cell it turns out the cell content is still in the mm/dd/yyyy format. Therefore, when attempt to insert the dates into mysql I only get 0000-00-00. I am attempting to use the STR_TO_DATE mysql function within my DBI do statement to correct this problem. My code looks like this:

$dbh->do("insert into frame                       

(indate,siteid,serial,contract,installdate) 

values            

(CURDATE(),\'$frame_siteid\',\'$frame_serial\',\'$frame_contract\',STR
+_TO_DATE($frame_installdate,\'%m/%d/Y\')");
[download]

However, this date formatting syntax is failing:

DBD::mysql::db do failed: You have an error in your SQL syntax; check 
+the manual that corresponds to your MySQL server version for the righ
+t syntax to use near '' at line 3 at ./excel.syr.frame.extract.pl lin
+e 91.
[download]

I imagine this is a common problem for which there is a common answer, but I am having trouble finding it on the mysql or perl sites/reference material I have searched.

Hagen Finley

Comment on DBI:mysql date conversion question Select or Download Code

Replies are listed 'Best First'.
Re: DBI:mysql date conversion question by lamp (Chaplain) on Sep 22, 2008 at 01:56 UTC
The following is the syntax of STR_TO_DATE. `STR_TO_DATE('00/00/0000', '%m/%d/%Y');` [download] In your insert statement, '%' symbol is missing for year(Y) format string. Also a bracket( ")" ) is missing at the end of the insert statement. And also check the value of '$frame_installdate' before the insert statement.	[reply] [d/l]
Re: DBI:mysql date conversion question by Anonymous Monk on Sep 22, 2008 at 01:14 UTC
use placeholders, What are placeholders in DBI, and why would I want to use them?	[reply]
Re^2: DBI:mysql date conversion question by Anonymous Monk on Sep 22, 2008 at 01:17 UTC
`my $sth = $dbh->prepqre(q~ ( indate, siteid,serial,contract,installdate) values (CURDATE(), ?, ?, ?, STR_TO_DATE(?,'%m/%d/%Y') ) ~); $sth->execute( $frame_siteid, $frame_serial, $frame_contract, $frame_i +nstalldate );` [download]	[reply] [d/l]
Re^3: DBI:mysql date conversion question by ikegami (Patriarch) on Sep 22, 2008 at 03:16 UTC
`my $sth = $dbh->prepqre(q~` [download] should be `my $sth = $dbh->prepare(q~ insert into frame` [download]	[reply] [d/l] [select]
Re^4: DBI:mysql date conversion question by Anonymous Monk on Sep 22, 2008 at 07:10 UTC

Back to Seekers of Perl Wisdom