| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 28 September 2005 : 19:32:14
|
Similar to the issue I had here:
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=59004
I decided to stay away from the DTS method and run a scheduled job that uses linked tables to import data on a nightly basis. One of my tables failed giving me the following error:
Error converting data type DBTYPE_DBDATE to datetime.
After going through the data I found a DOB field that had incorrect dates such as 1/14/200, and so on. Though I'm pretty sure I know what I'll have to do, I'd love it if I didn't have to resort to calling all the fields individually and performing a CAST or something like it. I have way too many tables and date fields to import so I'd like to stick to using the asterisk. Here is an example statement I am using in my nightly job:
SELECT * INTO wl_hous FROM
OPENQUERY(MLS_WL, 'SELECT * FROM wl_hous');
Is there an easy way I can prevent this from bombing out? |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 28 September 2005 : 21:47:57
|
quote:
Originally posted by ruirib
Why don't you correct the table data in the original DB?
Easier said than done.
I have been correcting the data as I find it, but I can't always be expected to correct the data, nor can I modify the code that is allowing the bad data. The database is ENORMOUS (hundreds of tables with millions of records) so it's a maintenance nightmare to always try and find a bad date.
The other problem is, sometimes it is not a bad date. For example, who's to say that 1/14/200 is a bad date? The year 200 did exist in our past, but when you try to import it into SQL Server, it treats it as a bad date. |
|
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 28 September 2005 : 21:58:38
|
First, valid dates in SQL Server go from January 1, 1793 to December December 31, 9999, so SQL Server can't handle dates outside these boundaries in Date/Time fields.
Second other than a DTS job, the only other way to have some control over what you do would be to a have a script to do the import. I'd rather go with a DTS job, since there you can have DTS do most of the job and just change some of the transformation ops... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 29 September 2005 : 04:54:15
|
I'd vote for DTS too - it's worth putting in the time and effort to get your jobs working robustly (and alerting you if they fail, etc)
fyi DTS with SQL2K5 is a very different beast, you may find it even more granular to add your checking with it. hth. |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 29 September 2005 : 21:55:59
|
quote:
Originally posted by ruirib
First, valid dates in SQL Server go from January 1, 1793 to December December 31, 9999, so SQL Server can't handle dates outside these boundaries in Date/Time fields.
really? man that sounds pretty lame. what would you do if you had a database that recorded historical events in date format prior to 1793?
I was using DTS prior to Linked Servers but ran into similar issues which I ended up resolving (somewhat). I really like Linked Servers because it is so much less work and I can query active data immediately. |
|
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 30 September 2005 : 04:29:01
|
from BOL
quote:
datetime and smalldatetime
Date and time data types for representing date and time of day.
datetime
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990
Microsoft® SQL Server™ rejects all values it cannot recognize as dates between 1753 and 9999.
smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.
I guess if you're keeping a resolution all the way down to one-three-hundreth of a second right up to 9999 (I guess they thought forward compatibility more important than historic back around Y2K time!), you count backwards until you run out of bits to store in - and get to 1793. It also raises the question of how far back it is useful to go? Year zero in Christendom isn't year zero in Buddhism/Hinduism, and is still a lot older than year zero in Islam!
If you ever *DO* need to keep data prior to 1793, you're unlikely to want to measure in three-hundredths-of-a-second, and indeed more likely to only have ddmmyyyy data, in which case just use char(8) to keep YYYYMMDD would be the more elegant solution. |
|
|
| |
Topic |
|
|
|
Topic Locked
