How to convert binary datatype to datetime using stored procedure?

Question

4.00/5 (1 vote)

See more:

i want to covert binary datatype to datetime using stored procedure and parameters

i create the following stored procedure but it dosen't return correct value

SQL

create proc Binary_to_DateTime
(
@mydate_bin binary
)
as begin
SELECT CAST(@mydate_bin AS DATETIME)
end

when execute this proc using this binnary value (0x0000000000000000000000000000000000000000000000008C3000000000)

exec Binary_to_DateTime 0x0000000000000000000000000000000000000000000000008C3000000000

it returns 1900-01-01 00:00:00.000 which is not correct

the correct value is 1998-04-05 00:00:00.000

How to make it return the correct value?

Thanks

Posted 5-May-11 9:48am

progahmed

Add a Solution

3 solutions

Solution 1

How many bytes of storage does a datetime value use - from memory I think it's typically 8? How many bytes of binary data are you feeding to the cast - looks like about 30 - I can't count that many zeroes reliably :-) The return value you are getting looks like a big ZERO to me [i.e. the base point for counting dates / times] - so maybe the cast is just looking at the first 8 bytes (? big-endian / little-endian issues ?). If your binary data was the right size you might get the answer you want. Worth a try?

Posted 5-May-11 10:18am

NuttingCDEF

Comments

progahmed 5-May-11 16:29pm

you are right
thanks NuttingCDEF

Solution 3

It should be binary(8) according to the MSDN documentation:

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

i.e. this round-trips:

SQL

select cast(convert(datetime, '2013-12-01 1:23 PM') as binary(8))
select cast(0x0000A14400DC8CF0 as datetime)

Posted 13-Feb-13 12:54pm

Dan Glass

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

dbrenth · Accepted Answer · 2011-05-05T10:22:00

Solution 2

Try an argument to your binary declaration:

alter proc Binary_to_DateTime
(
@mydate_bin binary(30))
as begin
SELECT @mydate_bin
SELECT CAST(@mydate_bin AS DATETIME)
end

Posted 5-May-11 10:22am

dbrenth

Comments

progahmed 5-May-11 16:28pm

that's right

thanks a lot for your help