DBD::ODBC and date/time/datetime/timestamp

Over the last few months I've been receiving an increasing number of emails from people experiencing problems inserting date/time/datetime/timestamp values into MS SQL Server specifically although not exclusively. There are a number of MS SQL Server ODBC drivers but the ones I see the most (in no particular order) are a) MS SQL Server ODBC Driver b) MS SQL Server Native Client driver c) Easysoft's SQL Server ODBC Driver and d) freeTDS. I hope to write a more expansive tutorial on this in the near future but until then I hope this blog post will help. The first thing many people hit problems with is attempting to insert into a column defined in MS SQL Server as a timestamp - don't, you can't. The timestamp column in MS SQL Server is intended to be used as a primary key and if you use it then set it to default since you cannot insert into it. The second primary problem is that DBD::ODBC attempts to use the ODBC API SQLDescribeParam to describe parameters and this API is notoriously difficult to implement and often ODBC drivers a) don't support it at all b) support it but fail under some circumstances c) support it and report the wrong parameter types under some circumstances (often when using functions in the select). The following Perl code was run on Windows in Strawberry Perl under Windows against the MS SQL Server ODBC Driver and hopes to demonstrate the problems and best solutions:
use DBI qw(:sql_types);
use strict;
use warnings;
use Data::Dumper;

my $h = DBI->connect('dbi:ODBC:dtexam','xxx','xxx', {PrintError => 1});
eval {$h->do(q{drop table dtexam1})};

$h->do(q{create table dtexam1 (a datetime)});

my $s = $h->prepare(q{insert into dtexam1 values(?)});

# the following works because SQL Server date format is YYYY-MM-DD
# in this database


# the following fails because it is not in the correct date format
# DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]
# Invalid character value for cast specification (SQL-22018)
eval {$s->execute('20100131');};

# the following should work for any ODBC driver even if it is not ms sql server
# the ODBC syntax for a date is {d 'YYYY-MM-DD'}
# this is by far the best way to insert a date via ODBC
# for times use {t 'hh:mm:ss[.fff]'} and timestamps use {ts 'yyyy-mm-dd hh:mm:ss[.fff]'}
$s->execute(q/{d '2010-01-31'}/);

# the following fails with most SQL Server ODBC drivers as SQLDescribeParam
# describes the parameter incorrectly as it is in a function (convert)
# The MS SQL Server ODBC driver I have reports the parameter as type 93
# when in fact it should be 12 for SQL_VARCHAR.
$s = $h->prepare(q{insert into dtexam1 values(convert(datetime, ?, 112))});
print Dumper($s->{ParamTypes}), "\n"; # prints 93 instead of 12

# the following works because the TYPE setting overrides what SQLDescribeParam
# returns (incorrectly) but it is not the optimum solution - use the
# ODBC date syntax above for maximum portability.
$s->bind_param(1, '20100131', {TYPE => SQL_VARCHAR});
print Dumper($s->{ParamTypes}), "\n";

# another alternative with DBD::ODBC 1.23_2 and above is to set
# odbc_force_bind_type so you can avoid setting TYPE on a per
# prepare basis but it has disadvantages:
# a) you no longer know which SQL statements fail SQLDescribeParam
# b) if you are using some unicode data you will run into problems
#    since you have forced the bind type to non-unicode
# Use the ODBC syntax without a bind TYPE for maximum portability
$s = $h->prepare(q{insert into dtexam1 values(convert(datetime, ?, 112))});
$s->{odbc_force_bind_type} = SQL_VARCHAR;