Click here to Skip to main content
12,633,519 members (31,883 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
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

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 10:48am
Rate this: bad
 
good
Please Sign up or sign in to vote.

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?
  Permalink  
Comments
progahmed 5-May-11 16:29pm
   
you are right
thanks NuttingCDEF
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Comments
progahmed 5-May-11 16:28pm
   
that's right

thanks a lot for your help
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
select cast(convert(datetime, '2013-12-01 1:23 PM') as binary(8))
select cast(0x0000A14400DC8CF0 as datetime)
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161208.2 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100