0

SQL Server does not offer a bit(32) data type (such as Postgres). I need to convert a string into a bit(32) and then into bigint and later on into a timestamp.

I can give an example:

I have to convert this string "x5c081ca4" into a bit(32) value: "01011100000010000001110010100100", then to bigint: 1544035492 and then to timestamp: "2018-12-05 19:44:52+01"

In Postgres these would be the functions: TO_TIMESTAMP(timeconv::bit(32)::BIGINT)

Any ideas how I can solve the problem?

Matzka
  • 125
  • 1
  • 13
  • You haven't described any problem yet. Convert what string into what? What does `such as Postgres` mean? With so little information, the only answer is `Yes to all` – Panagiotis Kanavos Aug 31 '20 at 10:36
  • 3
    Showing your intended input and output would help. – Jeroen Mostert Aug 31 '20 at 10:36
  • 2
    BTW why go through a `bit(32)` if the real target is an `int`? Is the real question how to parse a 32-bit hex string? – Panagiotis Kanavos Aug 31 '20 at 10:38
  • Does this answer your question? [MS SQL server - convert HEX string to integer](https://stackoverflow.com/questions/3604603/ms-sql-server-convert-hex-string-to-integer) – Panagiotis Kanavos Aug 31 '20 at 10:40
  • 2
    No need to go through a bit string at all. `SELECT DATEADD(SECOND, CONVERT(INT, CONVERT(BINARY(4), '0x5c081ca4', 1)), '19700101')`. Ironically, the harder part of this problem is converting the timestamp into local time, if that's what you need -- T-SQL can switch around time zones now but still has no simple function for the current local time zone name. You can convert the result to a `DATETIMEOFFSET` if you're OK with just the UTC time. – Jeroen Mostert Aug 31 '20 at 11:03
  • How would that conversion look like? `TODATETIMEOFFSET(DATEADD(SECOND, CONVERT(INT, CONVERT(BINARY(4), '0x5c081ca4', 1)), '19700101')), CURRENT_TIMEZONE())` But that doesn't work. – Matzka Sep 02 '20 at 09:04
  • Instead of `CURRENT_TIMEZONE()` I also tried `SYSDATETIMEOFFSET()`, without success. – Matzka Sep 02 '20 at 09:07

1 Answers1

1

SQL Server has a binary data type:

select cast(cast('abcd' as binary(4)) as int)

The length is in bytes and not bits, but it probably does what you want.

Note: ints are four bytes, so you don't need a bigint.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786