Sql query to convert varbinary to datetime

Question

Sql query to convert varbinary to datetime

grkanth81

Ten Centuries

Points: 1194
More actions
May 20, 2015 at 8:09 pm

#148897

Hello guys,
I would like to get the sql query to convert varbinary to datetime.
Here is the value in Varbinary(112) :
0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730
The expected result should be - 1970-05-29 00:00:00.000
Can you please let me know how to get the above result?
Thanks,
grkanth81

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

grkanth81 (5/20/2015)
Hello guys,
I would like to get the sql query to convert varbinary to datetime.
Here is the value in Varbinary(112) :
0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730
The expected result should be - 1970-05-29 00:00:00.000
Can you please let me know how to get the above result?
Thanks,
grkanth81

Interesting.

For that date, I get 0x0000647300000000 as the varbinary(112) equivalent.

Converting your string produces a series of spaces and then an ellipsis for me.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

well, i can't tell what was supposed to be stored in the varbinary to obfuscate the date.

i assumed maybe i could convert it to int, and then do the # seconds since 1900 or 1970 or something, but that didn't work. that gave me

1930-05-31 21:48:32.000or 2000-05-30 21:48:32.000

SELECT CONVERT(int,0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730)

declare @NumSeconds int

SET @NumSeconds = 959723312

select dateadd(second,@NumSeconds,'19000101') ,dateadd(second,@NumSeconds,'19700101')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

grkanth81 Ten Centuries Points: 1194 More actions · Answer 3

Thanks guys for your replies.

unfortunately that was an issue from the vendor. They initially said that it is a date field and that was the reason I was trying to convert it to date format.

But later after several discussions we had to do the following to resolve it.

Select LTRIM(Convert(varchar(112), 0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730, 0) )

The result set for the above will be - 299470

And then the vendor said for their software the dates will be calculated by subtracting the above result with 999999 which will result in YYMMDD date format

i.e. 999999 - 299470 = 700529 (so this result is in the for of YYDDMM format)

Thanks again guys and sorry for confusion.

Thanks,

grkanth81

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

grkanth81 (5/21/2015)
Thanks guys for your replies.
unfortunately that was an issue from the vendor. They initially said that it is a date field and that was the reason I was trying to convert it to date format.
But later after several discussions we had to do the following to resolve it.
Select LTRIM(Convert(varchar(112), 0x20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020323939343730, 0) )
The result set for the above will be - 299470
And then the vendor said for their software the dates will be calculated by subtracting the above result with 999999 which will result in YYMMDD date format
i.e. 999999 - 299470 = 700529 (so this result is in the for of YYDDMM format)
Thanks again guys and sorry for confusion.
Thanks,
grkanth81

um yeah - would have never figured that one out. Why do they insist on obfuscating things to that degree like that?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 5

SQLRNNR (5/22/2015)
um yeah - would have never figured that one out. Why do they insist on obfuscating things to that degree like that?

They probably have no clue that's what happened. As typical, they saved stuff as a varchar that's too big and fully padded, to boot. The subtraction stuff is probably the result implicit conversions due to something like a 1's compliment or something crazy like that to avoid setting the left-most bit which would make it negative, yada-yada-yada.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)