Serial Date Conversion in PSQL

[jbecker614]

I work with an application that uses a date format of the number of days passed since 01/01/0001.

Would any one know of the correct function to use to convert into a date through PSQL. I can do the conversion usind DateSerial after linked to MS SQL or MS Excel, but I was curious to see what the equivalent would be when creating a PSQL view from the Pervasive Control Center.

For Example, our application would store 732342. If I export into excel, I can subtract 693594 from it to get a value of 38748. This value represents the number of days since 01/01/1901. When formatted as a date in Excel it would then show correctly as 01/31/2006.

 

[mirtheil]

What version of PSQL are you using? There isn't a DateSerial function built into PSQL but if you are using v9 or later, you might be able to create your own function.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[jbecker614]

PSQL 9.

I think that I found the function:
TIMESTAMPADD ( SQL_TSI_DAY, "table name"."field.name", CONVERT('0001-01-01', SQL_TIMESTAMP) ) -1