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.