Postgresql cast hexadecimal to timestamp (when mig

Question

I have the following problem when parsing SQL Server insert queries to PostgresSQL. After making the necessary changes to the queries in order for the SQL statements to work, I have been having some troubles with the CAST() function.

CAST(x00009DD500000000 AS timestamp)

1. If I write CAST(0x000....), pgAdmin says that there is a syntax error near x

2. If I remove the 0, I get the error that the column does not exist

I was thinking it could be an error with the amount of columns, but since I am working with a lot of data and I haven't been able to see a function which converts a hexadecimal to timestamp in postgresql, I wanted to know if that could be causing some other kind of error. Maybe?

Is there a function to convert an hexadecimal value to timestamp in Postgresql?

Because converting the hexadecimal value to another type of data would take to much time.

Thanks in advance!

Answer 1

This timestamp notation is specific for MSSQL. It has two parts:

• date part - days since 1900-01-01
• time part - with 1/300s precision

There is no automatic cast for this value in PostgreSQL. Exact solution you can find here: how to cast the hexadecimal to varchar(datetime)?