SQLTeam.com | Weblogs | Forums

Sql query to convert varbinary to datetime


Hello guys,

I would like to get the sql query to convert varbinary to datetime.

Here is the value in Varbinary(112) :

The expected result should be - 1970-05-29 00:00:00.000

Can you please let me know how to get the above result?



how you get the value above?


The value which you mentioned above doesn't seems to be correct check out the below queries

select cast(convert(datetime, '1970-05-29 00:00:00.000') as varbinary(112))
result: 0x0000647300000000

select cast(0x0000647300000000 as datetime)
result: 1970-05-29 00:00:00.000


The value which you mentioned is not var binary but its is binary value equivalent of 1970-05-29 00:00:00.000

Go through this post it might help you understand better


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.