in reply to Re^4: Issue while inserting Thai Characters into Database..
in thread Issue while inserting Thai Characters into Database..

Dates/times can be tricky and are best inserted with ODBC syntax e.g., {ts 'yyyy-mm-dd hh:mm:ss[.fff]}, {d 'yyyy-mm-dd'} and {t 'hh:mm:ss[.fff]'}. If you omit parts it can lead to the error you are getting. See ODBC Datetime Format and Why do I get invalid value for cast specification (22018) from SQL Server when inserting with parameters?

Replies are listed 'Best First'.
Re^6: Issue while inserting Thai Characters into Database..
by ajaykumarb (Initiate) on Jan 21, 2010 at 11:21 UTC
    Thanks for your response Martin.
    I tried inserting date in SQL Server directly, it is allowing me 'YYYY-MM-DD' and 'MM-DD-YYYY' formats. It is getting displayed as 'MM/DD/YYYY HH:MM:SS AM'format.
    But when i try inserting from Perl it is giving errors as shown below. 

    my $sth = $dbh->prepare(<<SQL) or die "$DBI::errstr\n";
INSERT INTO BK_imageINfo (BusDate) VALUES ({ts ?})
SQL
*******OR*******
my $sth = $dbh->prepare(<<SQL) or die "$DBI::errstr\n";
INSERT INTO BK_imageINfo (BusDate) VALUES ({d ?})
SQL

$sth->execute($bdate);

    [download]
    Input1. my $bdate = "2010-01-20 00:00:00";
    ERROR: DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverSQL ServerIncorrect syntax near '@P1'. (SQL-42000).
    MicrosoftODBC SQL Server DriverSQL ServerStatement(s) could not be prepared. (SQL-42000)

    Input2. my $bdate = "01-20-2010 00:00:00";
    ERROR: DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)

[reply]
[d/l]

      Don't do that, make the whole parameter the string "{ts 'MYDATEHERE'}". You cannot stick a parameter in the middle of the ODBC date/time syntax, the parameter needs to be the whole thing.

      my $sql= q/INSERT INTO BK_imageINfo (BusDate) VALUES (?)/;
.
.
.
execute(q/{ts '2010-01-20 00:00:00'}/);

      [download]
[reply]
[d/l]
        I tried several options with timestamp. But i got "MicrosoftODBC SQL Server DriverInvalid precision value (SQL-HY104)" error message.
        But below code with date and no timestamp is working fine without any issues. 
        my $sql= "INSERT INTO BK_imageINfo (BusDate) VALUES (?)";
.
my $bdate = "2010-01-20";
$sth->execute("{d '$bdate'}");

        [download]
        Through my script, now i am able to insert date as well as Thai Characters to SQL Server database without any issues...

        Thanks much for your extensive support Martin.

        Thanks,
        Ajay
[reply]
[d/l]

In Section Seekers of Perl Wisdom