Sql query to convert varbinary to datetime

Transact-SQL
1

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,
ravi.

2

how you get the value above?

3

Ravi:
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

4

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
http://www.sql-server-helper.com/error-messages/msg-210.aspx

5

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,
Ravi.