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: 0xDD3FC20120A42 A94000000000000 0000 - 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?
8 3365 NeoPa 32,569
Recognized Expert Moderator MVP
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.
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: 0xDD3FC20120A42 A94000000000000 0000
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><9 4>
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_Gene ral_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 32,569
Recognized Expert Moderator MVP !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 32,569
Recognized Expert Moderator MVP !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.
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.1656761169 43) 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 32,569
Recognized Expert Moderator MVP
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)?
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_helpcollat ions()
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 32,569
Recognized Expert Moderator MVP !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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: ohaya |
last post by:
Hi,
I'm working with a date string with a format as follows:
Sat Dec 25 21:32:59 EST 2004
and I want to compare it to another date string of the format:
7/28/2004
|
by: Annie D via AccessMonster.com |
last post by:
Hi,
I am using the following SQL statement courtesy of access monster user:
'Terry Kreft ' to convert dates from a text format (20020331 00:00:00) to a
recognised date/time format (31/03/2002).
SELECT
END_DTTM,
Left(,4) AS END_DTTM_year,
Mid(,5,2) AS END_DTTM_month,
|
by: dubing |
last post by:
Hello everyone,
There is a field of DATETIME type in our Access 2000 database. PHP is
used to query the data and display the query results on the web. Does
Access provide any function that can convert DATETIME field into a more
user friendly format directly in query? I can do the following in
MySQL. How should it be done in Access?
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
|
by: prabhunew2005 |
last post by:
I am using postgres,php in web designing. I have to convert date entered in MM-DD-YYYY format to YYYY-MM-DD format. I have to use resulting date format in my search sql. I used todate method. But i did not get correct answer. Can you give me a solution for this?
(37 minutes ago )
|
by: AA Arens |
last post by:
I created at a Word filke containing field and the data is from my
database (via ODBC).
One of the fields is Date. The format in the database is dd-mm-yyyy (I
checked it in VB code), but in the Word file it is yyyy-mm-dd.
The Regional Settings from Windows is set to dd-mm-yyyy.
How to have the fields in Word listed to this format as well? The same
field also add time as 00:00:00 while there is no Time issue at all.
| |
by: ManickavasagamSathiskumar |
last post by:
hai
I have one text box in asp page
it for date entry field(dd/mm/yyyy)format user entered.
But when i store in database it will store (mm/dd/yyyy) format
how to change in asp.
|
by: chandru8 |
last post by:
I read the date from Notepad and convert it into date using Dateserial function. I try to convert it into my desired format like fomat( ,"dd/mm/yy") but I am unable to do that.
Dim fs As New FileSystemObject
Dim ts As TextStream
Dim str As String
Dim int1 As Integer
Dim dt1 As String
Dim dt As Date
Dim date1 As Date
Dim DATE2 As Date
|
by: thomasc1020 |
last post by:
This is regarding VB.NET 2003.
Variable 'Date' is a string and it contains date information in this
format: "DEC/05/2007".
Now I am trying to convert the format as "2007-12-05".
Is it possible to convert the arrangement using 'Format' method?
If so, please educate me how I can do such operation.
Thank you!
|
by: Chittaranjan |
last post by:
Hi All,
I am not sure how to change the date format in PostgreSQL. I have the dates stored in the database as yyyy-mm-dd HH:MM:SS+TimeZone to get the GMT time (Ex: 2008-02-28 14:03:23+05:30). But I want these dates to be shown in the following format:
dd/mm/yyyy HH:MM:SS TimeZone (Ex: 28/02/2008 14:03:23 IST)So now while I am trying to fetch the dates from the database I am using to_char() as below:
to_char(field_name, 'DD/MM/YYYY...
|
by: anoop s |
last post by:
Hi,
In my web application I want fetch data from fixed width text file. I am using OLEDB connection for retrieving data. But the problem is the date format is dd/mm/yyyy in text file. Can I change it into mm/dd/yyyy format while fetching. Thanks in advance.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |