Place Holders and SQL convert

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

Hi Monks!

I need to convert a data type because I am getting this error:

The conversion of a varchar data type to a datetime data type resulted
+ in an out-of-range value.
[download]

My question is if this is the best way to use it:

my $sth = $dbh->prepare_cached(<<SQL);
update my_table set d_date = convert(datetime, ?, 120), name = ?
SQL
$sth->execute('2015-07-15', 'Foo');
[download]

Thanks for looking!

Comment on Place Holders and SQL convert Select or Download Code

Replies are listed 'Best First'.
Re: Place Holders and SQL convert by Corion (Patriarch) on Sep 17, 2015 at 18:00 UTC
Does your SQL work if you replace the placeholder `?` by your intended column value? Looking at some random Sybase documentation, I don't find the value 120 documented as a valid format specifier for `convert(...)`. I don't find any value corresponding to `yyyy-mm-dd`, but you should not need one anyway, because the default for dates is `yyyy-mm-dd`. If something else changes this value, you can maybe change it back by using: `set temporary option date_format 'YYYY-MM-DD';` [download]	[reply] [d/l] [select]
Re^2: Place Holders and SQL convert by Anonymous Monk on Sep 17, 2015 at 18:06 UTC
It could be this for MS SQL Server: `SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy` [download] But is the Perl format looked OK?	[reply] [d/l]
Re^3: Place Holders and SQL convert by Corion (Patriarch) on Sep 17, 2015 at 18:11 UTC
I am really, really confused. The error message you get is from your SQL server. It has nothing to do with Perl. You cannot change your data format without also changing your data. In your first example, you have yyyy-mm-dd, in this example you have mm-dd-yyyy . You will need to decide on one format and then check that both, your data and the other arguments to `convert()` match that.	[reply] [d/l]
Re^2: Place Holders and SQL convert by Anonymous Monk on Sep 17, 2015 at 18:10 UTC
The dates are coming in this format: `05/30/2015` [download]	[reply] [d/l]
Re^3: Place Holders and SQL convert by Corion (Patriarch) on Sep 17, 2015 at 18:12 UTC
Please make up your mind as to what format your data is in. Then consult again the documentation for the `convert()` function in your SQL server. Then re-read Re^3: Place Holders and SQL convert.	[reply] [d/l]
Re^4: Place Holders and SQL convert by Anonymous Monk on Sep 17, 2015 at 18:24 UTC
Re^5: Place Holders and SQL convert by Corion (Patriarch) on Sep 17, 2015 at 18:29 UTC
Re: Place Holders and SQL convert by poj (Abbot) on Sep 17, 2015 at 18:08 UTC
I assume this is Microsoft SQLserver (style 120 being yyyy-mm-dd hh:mi:ss 24h) and the error message is for some SQL without convert. The SQL shown works for me but do you really want to update all the records in the table with the same values ?. poj	[reply]