November 28, 2003 at 8:28 am
Hi, I am trying to sql server 2000 to get data from a propriety unix dbase via ODBC. When trying to get a field called startime I get data over flow error messages. This field on the unix server is in format HHMMSS. I can get this into Access and it appears as HH:MM:SS. IF I then use SQL or DTS to get it from Access, when I use a select statement it appears as YYYY-MM-DD HH:MM:SS i.e. 23:00:00 in Access appears in sql server as 1899-12-30 23:00:00.000
I am new to this and probably missing something basic, but HELP !!
November 28, 2003 at 8:41 am
When the value comes over it is probably stored as 230000 and SQL doesn't directly interpret. To correct stuff in : at position 5 then poisition 3 and the value will be 23:00:00 which SQL will then interpret as 1900-01-01 23:00:00.
November 28, 2003 at 9:28 am
Hi, I have since found that their is a bug with access (which is doing the data extraction for the unix db) and if a datetime field only has a time it treats the date portion as null. This gets interpreted as 1899-12-30 (or derivation of depending on you location) by sql server. Just need to stop MS ACCess from behaving like this and the problem will be resolved
December 1, 2003 at 6:55 am
Umm, you can't stop Access (or SQL Server for that matter) from interpreting NULL Dates as "12-30-1899".
There are two solutions. Read in the data using DTS and make a simple ActiveX Script when copy that specific field and combine it with the other Date Field (I'm assuming there is another date field because the one you talk about here only has the Time). You can then Add both the Date and the Time together into one field.
The 2nd solution is to forget about it, and when performing queries, combine the 2 fields into one to form the date (in other words make a VIEW).
December 1, 2003 at 8:06 pm
because the date part is missing Access is setting it as 1899-12-30 and sql will do the same using 1900-01-01
if you don't care about the date part just dont use it in your queries and make sure to format the result when you read those appropriately
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply