May 16, 2005 at 2:26 am
Hi,
I am getting an overflow error with a date column - value is 31/12/2099
Insert error, column 4 ('Date',dbtype_dbtimestamp), status 6, Data Overflow. Invalid character for cast specification.
Is there some way around this?
TIA
May 16, 2005 at 5:26 am
Have you tried using SET DATEFORMAT DMY ?? Put this at the beginning of your statement and see if this works...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 16, 2005 at 5:37 am
AJ,
I've just been using the wizard and getting this error. The Date field from an accounting dataset has an incorrect date 31/12/2099. Its been reversed but the transactions still exist and the DTS import throws a wobbly.
I thought dates up to 4000+ were acceptable - so why is it rejecting ?
Richard
May 17, 2005 at 6:38 am
What date format do you normally use? MDY (month-day-year) or DMY (day-month-year) ?
As AJ mentioned, it looks like you use MDY (so the date should be written 12/31/2099). To store the date written as '31/12/2099', you need execute the statement SET DATEFORMAT DMY, or transform the date into another format ('20991231' should always work). Since you are using DTS, you'll probably have to transform the data in the DTS package.
The maximum allowable date is 12/31/9999.
May 17, 2005 at 6:54 am
Where can I set the SET DATEFORMAT DMY so that it applies to DTS operation
May 17, 2005 at 7:18 am
Open EM
Right click on the package name, and click Design Package.
On the package design menu, click Task, then 5 Execute SQL Task.
From the listbox labeled Existing Connection, select the connection number for SQL Server. Since you are importing data, it is probably connection 2 (or greater).
In the edit box label SQL Statement:, type SET DATEFORMAT DMY
Click the Execute SQL Task icon you just create once to highlight it.
Control+click the icon of what was previously your first task once.
Both icons should now be highlighted.
From the menu, click Workflow, then On Completion (or On Success, if you want).
Save the package.
Execute the package.
May 18, 2005 at 1:37 am
MKeast,
Thanks for the above info. I tried your steps but to no avail yet. I will retry again later.
Many Thanks
Richard
May 18, 2005 at 6:14 am
One more thing to note is that SET DATEFORMAT will only work if all dates in all columns are received in the same format. If some dates are represented textually as 'mm/dd/yyyy' and others as 'dd/mm/yyyy', then using SET DATEFORMAT won't help. In that case, you will have to convert the date to a valid format. As mentioned earlier, the safest input format is YYYYMMDD (i.e. '20991231'). That format works no matter what the DATEFORMAT setting is.
August 12, 2005 at 3:24 pm
To add to this question a little bit, I have a DB2 database that I'm trying to extract information from and put it into a SQL Server table. The StartDate field contains dates and other numbers that are not date formatted. My DTS is giving the same error pretty much. I tried adding some ActiveX Scripting to the DTS, but still fails. Basically I said -
If IsDate(DTSSource("STRTDATE")) Then
DTSDestination("STRTDATE") = DTSSource("STRTDATE")
Else
DTSDestination("STRTDATE") = ""
End If
I thought that would be pretty logical, but I guess not. If there's any way to scan the fields to see if they're a valid date format and bring them over, and mark fields as NULL if it encounters a field where it's not a valid date format, I'd appreciate any help.
Thanks!
August 16, 2005 at 7:22 am
You're not trying to save to a smalldatetime field are you? Maximum allowed value for this is June 6, 2079.
--
Scott
August 16, 2005 at 10:42 am
Scott,
The field is set at datetime, not smalldatetime. The problem is that in this field, the dates such as 10/1/199 or 12/1/200 exist in this field and what I'd like to do is check to see if it's a valid m/d/yyyy format. If not, I would like for SQL to just leave that field NULL and move to the next item. If the date is valid as m/d/yyyy or mm/dd/yyyy (9/30/1965 or 12/1/1963) then it will copy that information over to the new table.
Thanks for the reply.
============================================================
New Info
I was able to accomplish what I wanted by setting the field as a varchar(50). It did the logic and set invalid formats to NULL and valid formats copied over. However, the field is now a varchar instead of datetime. Is it possible to reconvert that field back to a datetime after the script runs so that it's a valid datetime field?
Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply