smalldatetime convert problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

smalldatetime convert problem

Author

Topic

newbb
Starting Member

4 Posts

Posted - 2011-10-25 : 13:01:05

Hello,

I need to insert data to database db001. The data I need to insert is in hex format and my script should convert it to actual date/time format. See the following three examples, what's wrong with the latest?

1.
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x00000000)) - Works

2.
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x9aef0000)) - Works

3.
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x00051900)) - Not working

Latest gives me this error message:

Conversion failed when converting datetime from binary/varbinary string.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-25 : 13:43:00

What does the Hex value represent? Is it some sort of offset?

EDIT: it looks like you are getting an Arithmetic overflow error.

SELECT CAST(0x00051900 as INT)

SELECT CAST(CAST(0x00051900 as INT) AS SMALLDATETIME)

newbb
Starting Member

4 Posts

Posted - 2011-10-25 : 14:35:33

Thanks for the quick response.

The hex value represents day when something happened, I guess it's a day when person got paid in this case. Please,
see the picture what the result looks like in the database when I executed the second working example to my test database.
I guess the problematic hex value should also give some date.

2. example in database:

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-25 : 15:33:04

It is odd to represent dates as hex values. Can you post the expected dates based on some hex values?

Be One with the Optimizer
TG

newbb
Starting Member

4 Posts

Posted - 2011-10-26 : 07:11:52

Here are five working values and below is screenshot from the actual database table. Dont mind the other values..

Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x94650000)
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x94610000)
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x9e410000)
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x99620000)
Insert into db001.dbo.table1 ([payday]) values(CONVERT(smalldatetime, 0x97400000)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-26 : 12:44:14

EDIT: Ignore this response. I was thinking in terms of INTs and offsets for some reason.. :)

Well the answer is pretty simple, SQL can't convert that to a date time.

So you can convert a number to a date easily enough, based on SQL storing them as numbers. However, the SMALLDATETIME only holds values from 1900-01-01 through 2079-06-06 or interval of about 65535 days. Since the converstion of 0x00051900 to an INT is 334080 and falls well outside that range, it won't work.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-26 : 17:37:20

Actually, those values went in fine. Can you post a (valid) date value whose hex value doesn't convert.

Sql server stores smalldatetime as a pair of 2 byte smallints. The first smallint is some sort of day offset of 1900-01-01. The second smallint is a minute offset from 0:00:00. So just looking at your hex values you can tell that the time portion is 00:00 (by the last 2 bytes)


set nocount on
declare @t table (payday smalldatetime)
Insert into @t ([payday]) values (CONVERT(smalldatetime, 0x94650000))
Insert into @t ([payday]) values(CONVERT(smalldatetime, 0x94610000))
Insert into @t ([payday]) values(CONVERT(smalldatetime, 0x9e410000))
Insert into @t ([payday]) values(CONVERT(smalldatetime, 0x99620000))
Insert into @t ([payday]) values(CONVERT(smalldatetime, 0x97400000))

select * from @t

output:

payday
-----------------------
2004-01-05 00:00:00
2004-01-01 00:00:00
2010-12-03 00:00:00
2007-07-05 00:00:00
2006-01-05 00:00:00

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-26 : 17:52:51

Looks like your initial value:
>>values(CONVERT(smalldatetime, 0x00051900)) - Not working

doesn't work because of the time portion (0x1900)
That seems to be too many minutes past 12:00 AM


select convert(binary(4), convert(smalldatetime,'1900-01-01 23:59'))
returns: 0x0000059F

select convert(smallint,0x059F) 
returns 1439 --smallint value representing 23:59

select convert(smallint, 0x1900)
returns 6400 --which is a valid smallint but apparently blows out the time portion of smallDatetime

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-10-26 : 17:58:26

quote:
Originally posted by TG

<snip>doesn't work because of the time portion (0x1900)
That seems to be too many minutes past 12:00 AM
<snip>

+1, I was just composing a post expaining this. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-26 : 19:57:56

When you take the integer values out of the picture and just think in hex then it is pretty simple. The first 2 bytes are days offset from 19000101.
So when you work through all the hex values from 0x0000 through 0xFFFF you get dates from 1900-01-01 through 2079-06-06.

When you convert those values to smallints it gets funky. When you get half way through the hex values (up to 0x7FFF) you reach the maximum smallint value: 32767. The next hex value (0x8000) is the minimum smallint: -32768. From 0x8000 through 0xFFFF the converted smallint values increment back from -32768 to -1.
For the time portion (the right 2 bytes), there are only 1440 minutes in a day which is well below the smallint max value so the converted integer values make more (human) sense. I say let the internal engine deal with binary pairs - we humans should stick with string notation ('20111026 17:58')

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources