How to convert odd date format from database

[!NoItAll]

I am converting about 600-thousand records from Microsoft SQL. The original database designer is, lets just say, "uncooperative."
I have several fields that represent date, times, etc. I cannot figure out how the date is stored.

• I know the field [a varbinary(16)]
• I can get the value
• I have several known date sample records

Here is an example:
The following record I know for certain corresponds to the date of 8/9/2002 (month-day-year).
The value in the date field (which is a varbinary(16)) in the database is:

0xDD3FC20120A42A940000000000000000 - I know this is the date field.

I have tried the following:

Select Convert(char(16), date) from...

I then get the number with the following loop
(looking only at the 4 most significant bytes)

    For I = 4 To 1 Step -1
 
        lClockTicks = lClockTicks * 256#
        lClockTicks = lClockTicks + Asc(Mid$(xDate, I, 1&))
 
    Next I
 

I get 19,742,630 hoping to find something like a Unix Epoch, but that number does not represent the correct date when treated like an Epoch.

Sooo - I am lost.

Anyone have any advice?

[NeoPa]

I looked for various patterns in the data but saw none. Clearly your first task is to identify the form the date is stored in. Without this there can be no progress elsewhere.

The date (9th August 2002) in an Access date format resolves to an integer value of 37477, which when converted to Hex shows as &H9265. I couldn't see this anywhere in the hex characters shown, nor could I find anything that may resolve to 2002 (or &H07D2). Sorry.

[!NoItAll]

Thanks for looking at my problem Neopa!
I figured out that the problem is that the data being returned by SQL is actually incorrect! I haven't figured out that problem yet - I will likely start a new thread for that. Somehow the data in the date field is being returned incorrectly.
Here's what I mean:
In the query analyzer the data shows up as:

0xDD3FC20120A42A940000000000000000

However with the following select statement:

select convert(char(8), itemdate) as mydate from item_data where itemid=12345678
 

Instead of getting

<DD><3F><C2><01><20><A4><2A><94>

I get this mess

<A6><3F><2D><01><20><F1><2A><F6>

I think it has something to do with an incorrect collation setting because every character in the extended ASCII range (>7F) is returned translated. All of the characters in the lower ASCII range (01 to 7F) are returned properly.
The current Collation name is: SQL_Latin1_General_CP850_CI_AS
I think I need something that is designed for UTF8 - but I'm not sure...

By the way - the date is calculated as the number of seconds since 1/1/1601... Sheesh - it seems to me that we are lucky to know the actual year of any documents that old - let alone the date/time/second.
Des

[NeoPa]

!NoItAll:
By the way - the date is calculated as the number of seconds since 1/1/1601... Sheesh - it seems to me that we are lucky to know the actual year of any documents that old - let alone the date/time/second.

:-D

Is this integral or floating point. I couldn't get the number to resolve to an integer at all using Access. I remembered it is stored as Big-Endian, but it couldn't handle that many hex digits. The number of seconds since 1/1/1601 would be circa 12,673,670,400 or &H2E368DD0. It doesn't seem to fit the data you have, whichever way the data were read. I can only assume this is then stored as a long (high precision) floating point value.

[NeoPa]

!NoItAll:
convert(char(8), itemdate)

I expect your conversion is due to using char instead of something more concrete. If you're still interested in seeing the data this way I suggest you have a look for another type to convert it to. I'm afraid I don't know them well enough from here to say which would be better, but I could look for you if it were still important.

[!NoItAll]

The problem is that the number is too big for VB6. VB6 lacks the precision to convert this directly and calculate the date. Rounding causes VB6 to be way off. Because of this I first pull the field in as a series of chars. Taking the first 4 chars I convert those to a number and using a calculated divisor (201.165676116943) I am able to get the year/month/day. The last two bytes will then get me the time of day in seconds in a separate calculation.
It took a lot of trial and error to get to this point - but it has actually proven reliable.
So - I do need those chars - but the issue here is why are certain chars (chars > 7F) being "translated" - how to I tell SQL to stop doing that? This wasn't happening is several earlier projects - but now it is. I still suspect it has something to do with collation. How can I change the collation?

[NeoPa]

I don't doubt you need the data. What I am suggesting is that char() is probably not the type to convert it to (for exactly the reason you're falling over). Have you done as I suggested and explored alternatives from somewhere like Data Types (Transact-SQL)?

[!NoItAll]

I see where you are going - I think...
There are several other conversion types to explore. I have tried all of the numeric ones in my SQL (decimal, numeric, float, bigint, etc) but all produce "0" (Zero) or an error.
There is a datetime - but that always returns "1901-01-01 00:00:00" - which I suspect is a default when it gets confused. I tried DateTimeOffset - but that just returns an error and timestamp returns 4 question marks.
I did figure out how to change the collation!
This has fixed my problem. I executed the following commands:

select * from ::fn_helpcollations()

This gave me a list of all the available collations. I chose a straight binary one - assuming no translation would take place with this in place.

Latin1_General_BIN

So I then executed the following command (available from SQL 2000 and up)

alter database [my_database] collate Latin1_General_BIN

Voila! I am now getting the chars untranslated and my calculations now produce the correct dates.

I think you are right though - there is likely a much better way to do it then my hack hack hack (it gets a three hack award). With someone skilled in SQL - it's quite possible, I'm sure, to create a function to return the date as an actual ANSI date. I will explore that when I take my SQL Class...

[NeoPa]

!NoItAll:
There is a datetime - but that always returns "1901-01-01 00:00:00" - which I suspect is a default when it gets confused.

That's simply the date reflected by the value zero (0).

I was thinking along the lines of bit fields, but I lost my job a few weeks ago so don't have access to any SQL testing any more I'm afraid. It's frustrating, as I may have to stop helping in here and go back to focusing exclusively on the Access stuff.