August 5, 2005 at 1:23 am
I have a staging table holding data that I need to import into a table in an ingres database. The column holding the data in sqlserver that is causing me grief, has been created as [datetime] NOT NULL default '' . When I populate this column with an empty string it converts it to the date(1/1/1900 - very annoying).
I ultimately want to transfer this data into an ingres table with the same structure - this column having been created as column_name date not null default ' ' in ingres. I have to populate this column with an empty string and I am having problems transferring this data into this table due to this problem column..
In the transform data task i have tried cast the sqlserver column as an empty string but then this causes a conversion error when i execute the step..
Does anyone having any ideas how to solve this problem??
August 8, 2005 at 7:59 am
Hey,
Not sure how ingres handles datetime, but assuming that it will convert strings in the YYYYMMDD format into dates, this script might be helpful for you...
select CASE DATEDIFF(mi, [DATE_FIELD], '')
WHEN 0 THEN ''
ELSE CONVERT(VARCHAR(8), [DATE_FIELD], 112)
END
from
SQL Servers converts empty strings automatically to blank dates (meaning 1900/01/01 00:00:00), so if the difference between the blank date and the date in your field is 0 (minutes is the smaller measure I could use without causing an overflow...) you can select an empty string, otherwise you can select the date, but always converting it to varchar.
hope it helps...
Rayfuss.-
August 9, 2005 at 8:24 am
Hi,
Not sure if you are using the ODBC or the JDBC, but under the ODBC driver for ingres there are null date settings. If this doesn't work then I think you will find that the cast function you are trying to use will need to be converted to the equivalent date function from Ingres as the SQL syntax from memory is actually being past ti Ingres and hence causing the error.
Set logging on the DTS and then have a look at the errors in the log. Another thing to do is to set just in time debugging and get the transformfunction to go to debug when the error occurs.
Hope its helpful
Nigel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply