[#1010798] Cast-Exception with NpgsqlTimeStamp to System.DateTime

View Trackers | Bugs | Download .csv | Monitor

2010-04-29 08:41
Submitted by:
Bernd Pinter (bernd_pinter)
Assigned to:
Nobody (None)
Npgsql Version:
Cast-Exception with NpgsqlTimeStamp to System.DateTime

Detailed description
For PostgreSQL's Timestamp-Datatype earlier versions of NPGSQL (until 2.0.5) returned a System.DateTime object. Since 2.0.8 this will return an NpgsqlTimeStamp. Converting this NpgsqlTimeSTimp to CLR's System.DateTime produces an exception.

For details & testcode see


Date: 2010-07-10 01:36
Sender: Francisco Figueiredo jr.

Fixed in cvs.

Please, give it a try and let us know if it works ok for you.
Date: 2010-05-13 14:01
Sender: Francisco Figueiredo jr.


as it seems ok, then I'll start implementing the NpgsqlValue support in NpgsqlParameter. I'll start adding the testcases so we can check everything is working ok.

If you have any ideas or suggestions, please let us know on dev list. I'm going to start a thread there.
Date: 2010-05-12 06:22
Sender: Bernd Pinter

Hi Francisco!

It's realy OK not to implement the IConvertible Interface -
especially when SQLClint didn't do that. As i voted
yesterday: "+1 for NpgsqlValue"...
Date: 2010-05-11 18:02
Sender: Francisco Figueiredo jr.

Hi Bernd!

Don't worry about testing with Sql Server. As Josh said, SqlParameter has a Value and SqlValue property. The value property is returning a CLR type ( in this case DateTime ) and the code works ok.

Npgsql will have to do the same.

We wouldn't like to implement the IConvertible interface because it would be inconsistent with SqlClient. Although we may allow such inconsistences, we'd rather not need to.
Date: 2010-05-11 13:50
Sender: Bernd Pinter

Well, i tested the same programm with SQLServer2005 and
.Net3.5SP1. The cast-exception did NOT happen, because the
DateTime output-parameter returnd by the SqlClient is realy
a System.DateTime.
I can't say for what Systen.Data.SqlTypes.* are used for,
but -as i said- the Provider returns only native CLR types.
Can't find any useful hints for that SqlTypes on MSDN :-/

Here's the (modified) test-program:
using System;
using System.Data;
//using Npgsql;
using System.Data.SqlClient;

namespace weg2
class Program
static void Main(string[] args)

// create connection & command
SqlConnection con = new SqlConnection(@"<snip>");

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "tbl_dt_bug_insert";
cmd.Parameters.Add(new SqlParameter("@p_id",
System.Data.SqlDbType.Int, 0, ParameterDirection.Output,
false, ((byte)(0)), ((byte)(0)),
"id",DataRowVersion.Current, null));
System.Data.SqlDbType.DateTime, 0,
ParameterDirection.Output, false, ((byte)(0)), ((byte)(0)),
"timestamp_store", DataRowVersion.Current, null));

// build up the result DataTable with its
columns (as defined by the PgPLSQL function ouput-parameters)
DataTable tbl = new DataTable("test");
DataColumn col = null;
col = tbl.Columns.Add("id", typeof(System.Int32));
col.AllowDBNull = false;
//col = tbl.Columns.Add("timestamp_store",
col = tbl.Columns.Add("timestamp_store",
col.AllowDBNull = true;
tbl.PrimaryKey = new DataColumn[] {
tbl.Columns["id"] };

// define a single DataRow holding the PgPLSQL
function return-rowset (this will be the inserted row in the
DataRow row = tbl.NewRow();

// open connection, execute the command, fill
the data-row with the output-params of the PG stored-function
ExecuteNonQuery(cmd, row);

// just output the DataRow and wait for the user
System.Console.WriteLine("{0} | {1}", row["id"],
System.Console.WriteLine("<Press Enter>");


public static int ExecuteNonQuery(IDbCommand
command, DataRow row)
int ret = command.ExecuteNonQuery();

// copy the Output-Parameter into the (empty)
foreach (IDataParameter p in command.Parameters)
if (p.Direction == ParameterDirection.Output
|| p.Direction == ParameterDirection.InputOutput)
row[p.SourceColumn] = p.Value;
return ret;

Here's the stored procedure for SQLServer:
create procedure tbl_dt_bug_insert (@p_id int OUTPUT,
@p_timestamp_store datetime OUTPUT)
select @p_id = (CAST(RAND() * 100 AS INT)) ,
@p_timestamp_store = getdate()

You can call the stored_proc in TSQl directly with:
declare @i int;
declare @t datetime;
exec tbl_dt_bug_insert @p_id = @i OUTPUT, @p_timestamp_store
= @t OUTPUT;
select @i, @t;
Date: 2010-05-11 12:31
Sender: Gildas

+1 for NpgsqlValue too :)
Date: 2010-05-11 09:39
Sender: Bernd Pinter

+1 for NpgsqlValue.
Just because simpler to implement (each IConvertible needs
17 methods) and it did work until Npgsql 2.0.5
However, implementing the IConvertible-Interface will also
solve the problem (see below).

Back to my mystery behaviour:
Francisco was right. VS-Intelisense and the
VS-ImmediateWindow take use of the GAC registered Npgsql.
While the program itself take use of my own compiled Npgsql
(wich implements the IConvertible). Removing Npgsql from the
GAC solves this mystery.

Waht i learned from my tests yesterday: implementing the
IConvertible Interface solves the bug too! Perhaps it's
still not the worst idea to implement the interface in each
Npgsql datatype. Maybe i can help you typing in the code??

The library-code wasn't implemented by me. So it was a
little hard work for me to learn and copy out all necessary
codes (and not more). And i also don't know if someone runs
with SqlDateTime in the same error. But i guess no - if yes,
someone had definitely called me. Maybe because they didn't
work with SQLServer and the library yet?!

On the other side, we have some SQLServers in our company.
I'll test if the SqlDateTime will also throw this error and
wich type will really returned by microsoft's

I attached my "fixed" NpgsqlTimeStamp.cs file, which
implements the IConvertible Interface - so you all can see
what i have done yesterday. But you'll be warned: most of
the methods do nothing (except the ToDateTime method - just
for a proof of concept). My changes are between line 2992
and 3066.

thx for all your help,
Date: 2010-05-11 02:37
Sender: Francisco Figueiredo jr.

Hmmmm, nice catch.

+1 for NpgsqlValue. I also think it is the way to go.
Date: 2010-05-11 02:28
Sender: Josh Cooley

I did a quick look at the SqlParameter API to see if there was anything obvious that explained this. I found something unexpected, but definitely obvious. SqlParameter has a SqlValue property that returns the provider property type and Value must always return the CLR property type (with some condition for user set provider types).

Based on this, I would guess the solution is to go back to the earlier behavior for Value and add an NpgsqlValue property for provider specific types.

IConvertable may be a useful feature for the provider types, but I'm hesitant to add that if the types for SqlClient don't implement the interface.
Date: 2010-05-11 02:24
Sender: Francisco Figueiredo jr.

Got some interesting results here...

SqlDateTime throws the same exception.

Here is the code I used to test:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Npgsql;
using NpgsqlTypes;

using System.Data;

using System.Data.SqlTypes;

namespace Bug1010798
class Program
static void Main(string[] args)

// build up the result DataTable with its columns (as defined by the PgPLSQL function ouput-parameters)
DataTable tbl = new DataTable("test");
DataColumn col = null;
col = tbl.Columns.Add("id", typeof(System.Int32));
col.AllowDBNull = false;
col = tbl.Columns.Add("timestamp_store", typeof(System.DateTime));
col.AllowDBNull = true;
tbl.PrimaryKey = new DataColumn[] { tbl.Columns["id"] };

// define a single DataRow holding the PgPLSQL function return-rowset (this will be the inserted row in the database)
DataRow row = tbl.NewRow();

NpgsqlTimeStamp t = new NpgsqlTimeStamp(2010, 2, 2, 10, 10, 10);

SqlDateTime tt = new SqlDateTime(2010, 2, 2);

row["timestamp_store"] = tt;


Note that when running it, SqlDateTime will give you the same problem. I thought it should work.

Bernd, you said you want to use your code provider independent. Did you try with Sql Server? If so and it worked, then SqlClient isn't returning SqlDateTime for datetime values. Can you confirm which type it is returning?

Thanks in advance.
Date: 2010-05-11 02:20
Sender: Francisco Figueiredo jr.

As Josh said, implementing the IConvertible interface did the trick.

Bernd, I don't know why are you getting this.

Are you using Npgsql from GAC or compiled from code? Maybe Intelisense is showing you the result of your changes, but when running it is using Npgsql from GAC?

Date: 2010-05-11 01:59
Sender: Francisco Figueiredo jr.

I forgot that the explicit operator would only be called when there is a variable of the type where the operator is defined.

For example..
This works:
NpgsqlTimestamp a = new NpgsqlTimestamp(...);
DateTime b = (DateTime) a;

This doesn't:

NpgsqlTimestamp a = new NpgsqlTimestamp(...);
Object_value c = a;

DateTime b = (DateTime) c;

Note that this last line is what must be being done inside the DataRow[blahblah] indexer. So, when it receives the p.Value in original code, it is handled as an object and that's why the explicit operator doesn't work.

Again, I still don't get it how SqlDateTime would work. Unless there is specific code inside the indexer to handle SqlDateTime.

Date: 2010-05-11 01:53
Sender: Francisco Figueiredo jr.

Ok, adding the explicit operator didn't help. :(

I wonder how SqlDateTime works if it doesn't implement the IConvertable interface...
Date: 2010-05-11 01:27
Sender: Francisco Figueiredo jr.

Would it be possible that we are missing an explicit operator to DateTime to Timestamp type?

I can see an operator to TimestampTZ but not to Timestamp.

I noticed that SqlDateTime struct has an explicit operator to convert an SqlDateTime to DateTime.

Maybe internally the DataRow[] tries to cast the value to DateTime. If it is this case, the explicit operator would make it work.

What do you think?
Date: 2010-05-10 13:28
Sender: Bernd Pinter


I checked in the VS2008 ImmediateWindow the implementation
of the IConvertable interface. For a better understanding,
heres the little code:
NpgsqlTypes.NpgsqlTimeStamp ts = new
NpgsqlTypes.NpgsqlTimeStamp(2010, 1, 2, 3, 4, 5);
Console.WriteLine(ts is IConvertible);
In the ImmediateWin:
`ts is IConvertible`

Whereas the output of the program says:
2010-01-02 03:04:05

Mystery, i think.
Oh - and not to forget: the exception still happend. But i
don't know if i can count on that...
Date: 2010-05-10 13:15
Sender: Bernd Pinter

Yes, i think that (all) other types will have the same
problem too.

To help you, i tried to fix (implement the
IConvertible-Iterface) the NpgsqlTimestamp in that way:
*) I added "IConvertible" to the struct NpgsqlTimeStamp
*) I added all the required methods ToXXX(IFormatProvider);
Currently only ToDateTime() do anything real - all others
will throw an InvalidCastException.

As a little surprise to me, when using "my" fixed
test-version of Npgsql2.0.9, VS2008 knows (in the
Objectbrowser within my previously posted testprogram), that
NpgsqlTimeStamp has IConvertible as a Basetype. But the
Query "timestamp is IConvertible" returns FALSE, where
timestamp is an instance of NpgsqlTimeStamp?! And of course,
the program fails with the same exception-message "Unable to
cast object of type 'NpgsqlTypes.NpgsqlTimeStamp' to type
To be short: my test failed on some compiling/linking stuff
and i don't know why...
Date: 2010-05-10 12:45
Sender: Josh Cooley

IConvertable does seem to be the most straight forward solution to this. Since this issue can show up with other types, we will probably need to fix this for all types.
Date: 2010-05-10 07:46
Sender: Bernd Pinter


No, sorry. It's not possible for us to use the shown workaround:
object myObject = p.Value;
NpgsqlTimeStamp myNpgTs = (NpgsqlTimeStamp)myObject;
DateTime myDateTime = (DateTime) myNpgTs;

The Code i've posted was taken from a library (especially
the method 'ExecuteNonQuery(IbCommand, DataRow)') which
works vendor/provider independed. Thiss means the code
should work with Postgres, SQLServer, Oracle...

However, the exception says: "Unable to cast object of type
'NpgsqlTypes.NpgsqlTimeStamp' to type 'System.IConvertible'"
Can implemeting the IConvertable-Interface for the
NpgsqlTimeStamp be a solution for the problem? And if so,
should every Npgsql*-Datatypeclass implement this Interface?
Because if not, some developer would realy suprised to find
the interface present in NpgsqlTimeStamp, but not -for
example- in NpgsqlIntervall?

Tanks for helping,
Date: 2010-05-05 09:50
Sender: Gildas

I've tested the testcode with npgsql versions from 2.0.5 to 2.0.9 and it fails since the 2.0.8 version, this version has added new functionnalities to the Npgsql Date Datatypes.

It is possible to make it work if you do it like this :
object myObject = p.Value;
NpgsqlTimeStamp myNpgTs = (NpgsqlTimeStamp)myObject;
DateTime myDateTime = (DateTime) myNpgTs;

The the implicit converters of the NpgsqlTimeStamp will be called, instead of the ToString() method of the NpgsqlTimeStamp class.

I'll try to modifiy the NpgsqlTypesHelper.cs file (responsible of the bug, for me) especially the PrepareDefaultTypesMap method... And I let you know my results.

Attached Files:



Field Old Value Date By
File Added482: DateDatatypes.cs2010-05-11 09:39bernd_pinter
Powered By FusionForge