CODEWITHSUNDEEP
c#postgresql
Nishan
Nishan

Reputation: 2871

Postgresql error : String was not recognized as a valid date time

I am getting this error when I try to retrieve a date field from postgresql from C#. As in select datefield from table.

The system on which postgesql is running is on IST timezone. I can not edit any settings on the database or the system on which it is deployed. What can I do in my code to prevent this issue?

UPDATE: The client is on MST timzone. Forgot to mention that.

UPDTATE2 : Attaching code

DbDataAdapter myAdapter = dbFactory.NewDataAdapter(sSQL, myConnection);
if (myConnection.State != ConnectionState.Open)
       myConnection.Open();   
ds = new DataSet();
myAdapter.Fill(ds, dataTableName);

Upvotes: 0

Views: 2755

Answers (2)

jishi
jishi

Reputation: 24624

Are you sure you are using a valid dialect on your DbDataAdapter? What type does your factory return? Meaning, what does myAdapter.GetType() return?

PostgreSQL handles min/maxvalues a little different than most DBs, and might return dates as:

  • '-infinity' corresponds to DateTime.MinValue
  • 'infinity' for DateTime.MaxValue

Unless your adapter handles that I guess you might end up with a similar error.

I'm guessing you should be using NpgsqlDataAdapter from the Npgsql-provider, but you don't mention it somewhere.

EDIT: Found that there is a known bug: http://pgfoundry.org/forum/message.php?msg_id=1005522, but haven't found which versions this affect. It has to do with half hour timezones (e.g +3:30). I guess that is fixed in 2.0.8 as well.

http://pgfoundry.org/frs/shownotes.php?release_id=1686 some timestampTZ fixes was also present in 2.0.10

Upvotes: 3

Alex Hope O'Connor
Alex Hope O'Connor

Reputation: 9694

Try reading it as a string and then parsing it as a date in C#.

Like:

String date = GetFromDb();
DateTime realDate = DateTime.Parse(date);

See if that works, could just be a format error.

However if this dosn't help, Could you please post your code.

Thanks, Alex.

Upvotes: 0

Related Questions