0

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Juan Luis de Reiset
  • 11
  • 4

1 Answers1

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

Tomasz Myrta
  • 1,114
  • 8
  • 10