CODEWITHSUNDEEP
databasepostgresqltype-conversionsql-insert
Juan Luis de Reiset
Juan Luis de Reiset

Reputation: 11

Postgresql cast hexadecimal to timestamp (when migrating from SQL Server)

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!

Upvotes: 0

Views: 889

Answers (1)

Tomasz Myrta
Tomasz Myrta

Reputation: 1144

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)?

Upvotes: 1

Related Questions