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

I am retrieving a bunch of values out of one database, trying to fix the format and reinsert into a different master database, which would usually be fine, but 3 of my values are giving me a heck of a time. There are three numeric values in the first DB that I would like to format into a date time and insert into the new DB 

#Taking the values one at a time, and assigning to variables
$DOBYear = $ayDOB_Year[$i];
$DOBMonth = $ayDOB_Month[$i];
$DOBDay = $ayDOB_Day[$i];

#Concatonating the string
$ODBCDate = sprintf("%4d-%02d-%02d", $DOBYear, $DOBMonth, $DOBDay);

[download]

Up to hear everything is groovy, but when I try to insert this into the new SQL table I am getting the following error:

Error:
[242] [2] [0]
"[Microsoft][ODBC SQL Server Driver][SQL Server]
The conversion of a char data type to a datetime data type resulted in an out-of-range date time value."

Ok so then I tried casting it on the insert statement, and it still isn't working! 

$sqlInsert = "Insert into tblMaster (List_Name_, Gender_, First_Name_,
+ Last_Name_, Full_Name_, Address_1_, City_, State_, Zip_Code_, SCF_Co
+de_, Country_, Email_, Domain_Name_, TLD_, DOB_) Values ('$ListName',
+'$Gender','$FirstName','$LastName','$FullName','$Address','$City','$S
+tate','$Postal','$SCF','$Country', '$Email','$DomainName','$TLD', cas
+t('$ODBCDate' as datetime))";

[download]

When I print the SQL statement out to the window and paste into SQL it works fine!! Anyone have any insight??

janitored by ybiC: <code> tags around example code, <tt> around error message

Replies are listed 'Best First'.
Re: perl dates into string into SQL
by hmerrill (Friar) on Sep 17, 2003 at 20:19 UTC
    Unless I'm missing something, you don't tell which database you are using ODBC to access - which database is it?

    The book "Programming the Perl DBI" p. 293 describes DBD::ODBC date handling: 
      You can use ODBC escape sequences to define a date in a
  database independent way.  For example, to insert a date
  of Jan 21, 1998 into a table, you could use:

    INSERT INTO table_name (date_field) VALUES ({d '1998-01-21'});

  Similar escape sequences are defined for other date/time
  types.  Here's the full set:

    {d 'YYYY-MM-DD'}                  - date
    {t 'HH:MM:SS'}                    - time
    {ts 'YYYY-MM-DD HH:MM:SS'}        - timestamp
    {ts 'YYYY-MM-DD HH:MM:SS.FFFFFF'} - timestamp

    [download]
    and there's more, but you get the gist.

    Maybe the 'cast' is a MS SQL Server function that DBD::ODBC doesn't support - just a guess. That would explain why you can cut and paste the insert into an SQL Server client session and it works, but it doesn't work in DBI with DBD::ODBC.

    I'm not familiar with DBD::ODBC, but if I were you I'd check the DBD::ODBC perldocs to see if interaction with 'dates' is described in there. Do 
      perldoc DBD::ODBC

    [download]
    at a command prompt to read the documentation that comes with the DBD::ODBC module.

    Hope this helps.
[reply]
[d/l]
[select]
      hmerril has hit the nail on the head - you have a problem with converting your string representation of a date to the database representation of a date. MS Sql Server should (probably) automagically convert a datestring into a date if you present it in the expected format, whatever that is. I don't know MS Sql Server, but Oracle expects it as 'DD/MON/YYYY' (e.g. 17/SEP/2003). I haven't used ODBC in years, but I expect that hmerril is also correct in guessing it doesn't support the specific-to-MS-Sql-Server "cast" function. ODBC is database independent, so I bet the corresponding Oracle function "to_date" isn't supported either.
[reply]
Re: perl dates into string into SQL
by bear0053 (Hermit) on Sep 17, 2003 at 17:30 UTC
    when you format the data to insert do a print statement and post the value so we can see an example of the data you are trying to insert. it sounds like the format you are creating is wrong this will be easy to fix once you show us the values you are using
[reply]
Re: perl dates into string into SQL
by Plankton (Vicar) on Sep 17, 2003 at 17:49 UTC
    Using place holders couldn't hurt.

    Plankton: 1% Evil, 99% Hot Gas.
[reply]
Re: perl dates into string into SQL
by jenalice (Initiate) on Sep 17, 2003 at 17:36 UTC
    See the thing is if the values in the original database were NUll, it was setting the date to 0-0-0, which is invalid in SQL. So I did a test to see if the original values are NULL, then set the variable to ""(blank) which converts to 1900-01-01 00:00:000 which is a good date for SQL.
[reply]
      Don't do that. If the original values are NULL, then you should insert NULL into the new field. This is accomplished by inserting undef instead of a string. Having a bogus value "1900-01-01" is dangerous because it breaks the assumptions people and code make about valid values.
[reply]
[d/l]
      can u provide a printout of the sql state that perl providing. a valid sql date is: 9/16/2003 5:23:49 PM try making it this format
[reply]

Back to Seekers of Perl Wisdom