Click here to Skip to main content
11,411,840 members (62,702 online)
Rate this: bad
good
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 Smile | :) 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 at 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 at 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
0 OriginalGriff 268
1 Sergey Alexandrovich Kryukov 200
2 Sascha Lefévre 170
3 ProgramFOX 130
4 Maciej Los 110
0 Sergey Alexandrovich Kryukov 8,955
1 OriginalGriff 7,158
2 Maciej Los 3,480
3 Abhinav S 3,248
4 Peter Leow 3,059


Advertise | Privacy | Mobile
Web01 | 2.8.150414.5 | Last Updated 13 Feb 2013
Copyright © CodeProject, 1999-2015
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