PostgreSQL date column, UnsupportedFormatException

Databases
#1

When I try to assing a value to a date variable from a DatabaseRecord.DateColumn (PostgreSQL) the debugger returns an UnsupportedFormatException:

Dim myDate As Date myDate = myDatabaseRecord.DateColumn("myDateDbField") // Returns UnsupportedFormatException

The value that the debugger indicates for myDateDbField is 3487104000.

What am I doing wrong?

#2

How do you get a DatabaseRecord in this instance? Shouldn’t you have a RecordSet?

#3

Yes, first I get a Recordset, then I split it into DatabaseRecords. I’ve some classes with a method ( deserializeFromDbRecord(nDr As DatabaseRecord ) that populates its properties with the values of the fields of the DatabaseRecord assigned.

Everything is working fine except the fields of date format.

#4

Don’t use databaserecord as a storage medium. Use dictionary instead if you want name/value pairs.

#5

[quote=126787:@Carlos Arzuaga]The value that the debugger indicates for myDateDbField is 3487104000.
What am I doing wrong?[/quote]

Hello Carlos,

The value 3487104000 is the number of seconds since 12:00 AM, January 1, 1904 (See docs at Date Docs)

dim d as new Date d.TotalSeconds = 3487104000 MsgBox CStr(d.Year) + "/" + CStr(d.Month) +"/" + CStr(d.Day)

When The above code runs the value is 2014/7/2 - converted to conventional terms is 2 July, 2014.

Maybe try the following code:

Dim myDate As Date myDate.Totalseconds = myDatabaseRecord.DateColumn("myDateDbField")

#6

@Tim Hare : I’ve probably not explained well, my english is limited. I use the databaseRecords contained in a RecordSet to populate the properties of an instance of a bussines model class. The instances of these custom classes are the storage mediums.

@Eugene Dakin : Thanks Eugene, I’ve explored that solution, but it doesn’t work for me in this case, it’s very confused.
In theory myDatabaseRecord.DateColumn(“myDateField”) must returns a Date object, but in the inspector it seems to return a double (probably TotalSeconds, as you indicate). When I try those codes the debugger returns the next exceptions or compilation errors:

Dim myDate As Date myDate = myDatabaseRecord.DateColumn("myDateDbField") // UnsupportedFormatException, according to the documentation, this should work.

Dim myDate As New Date myDate.TotalSeconds = myDatabaseRecord.DateColumn("myDateDbField") // Type mismatch error, expected double but got date

Dim myDate As New Date myDate.TotalSeconds = myDatebaseRecord.DateColumn("myDateDbField").TotalSeconds // UnsupportedFormatException

#7

Dim d as new date
Dim s as string = d.SQLDateTime
Insert s as a string

Retrieve s as a string from recordset
Dim d as New date
d.SQLDateTime = s

#8

Thanks @Jym Morton I retrieve a date value from a RecordSet without troubles in others parts of my apps, but in this case I need to do it at DatabaseRecord level. It is working fine for all field types except for date types, returning the errors that I indicate in the previous message.

#9

Then don’t use date type, use a string.

#10

I had to read your reply several times. A recordset doesn’t contain a databaserecord. It does contain databasefields. If you’re pulling a value out of the recordset, use DatabaseField.DateValue.

myDate = myDatabaseRecord.Field(“myDateDbField”).DateValue

#11

@Tim Hare : Thanks, It has opened my eyes!!

The doc of the RecordSet begins saying: “A RecordSet is a group of Database records.”, and I’ve misinterpreted it as DatabaseRecords.

Now I’ve revised my code and know how to solve this.

My apologies for this mess and thanks everybody for all your help.

#12