bjennings
asked on
SQL 2005 importing text field data into a date field
Hello Everyone,
We use a sql as a backend for our reports and every night I have a ssis package which imports the data from our main system into the sql database. The main system for some unknown reason has a date field set as a text field (there is no way to change this) so I have to import the date as a text field which makes running reports more complicated. Also in the text field there are null values and also a vew records have "/ /" in that field, so it is not easy to change it to a date field after it is imported.
Is there a way I can modify the import to say something like "if textdate = null then "01-01-1900" or if textdate = "//" then "01/01/1900"
Thanks,
Bill
We use a sql as a backend for our reports and every night I have a ssis package which imports the data from our main system into the sql database. The main system for some unknown reason has a date field set as a text field (there is no way to change this) so I have to import the date as a text field which makes running reports more complicated. Also in the text field there are null values and also a vew records have "/ /" in that field, so it is not easy to change it to a date field after it is imported.
Is there a way I can modify the import to say something like "if textdate = null then "01-01-1900" or if textdate = "//" then "01/01/1900"
Thanks,
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am not sure if I follow..in the ssis I have a query
select field1, field2, textdate, field4 from mydatabase
Where do I enter the Case statement?
select field1, field2, textdate, field4 from mydatabase
Where do I enter the Case statement?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am importing the data from a progress database, so I am using a odbc connection import and I enter the
query like this
select "Field1", "Field2", Case When IsDate("textdate")=1 Then Convert(DateTime,"textdate ",101) Else '01/01/1900'
End as realdate, "field4" from "mytable"
When I parse it, I get an error message saying the statement could not be parsed.
The field realdate...Can I name that anything I want?
Thanks,
Bill
query like this
select "Field1", "Field2", Case When IsDate("textdate")=1 Then Convert(DateTime,"textdate
End as realdate, "field4" from "mytable"
When I parse it, I get an error message saying the statement could not be parsed.
The field realdate...Can I name that anything I want?
Thanks,
Bill
Yes, realdate was just a name I made up to give the value a column alias.
ASKER
So I am thinking I can't use that import query with a progress database import, but that is okay I am trying to use this query as an update query after the import and I am running into a problem. I get the following error:
Conversion failed when converting datetime from character string
Now I tried to troubleshoot this by querying all the null values and updating them to 01/01/1900 and then ran a straight set textfield = convert(datetime,textfield ,101) and still got that error
THen I modified the column type to a datetime and and I was successful, but I can't do that everyday.
Why can I convert it to a date in a query???
Thanks,
Bill
Conversion failed when converting datetime from character string
Now I tried to troubleshoot this by querying all the null values and updating them to 01/01/1900 and then ran a straight set textfield = convert(datetime,textfield
THen I modified the column type to a datetime and and I was successful, but I can't do that everyday.
Why can I convert it to a date in a query???
Thanks,
Bill
try this query to see what values are causing the problem.
Select textfield from yourTable where IsDate(textfield) = 0
then you will know what strings can't be converted to dates and do something with them like you did the null values.
Select textfield from yourTable where IsDate(textfield) = 0
then you will know what strings can't be converted to dates and do something with them like you did the null values.
ASKER
That is the weird part...I did that and it gave me all nulls and / / so I then ran an update query to set the textdate = '01-01-1900' then I ran isdate(textfield) = 0 again and no records came back. That is when I thought I could convert it and it gave me the error.
Conversion failed when converting datetime from character string
Conversion failed when converting datetime from character string
ASKER
I am finding some dates in the textdate filed with 4 character year and some with 2 character year...Could this be the reason that it can not convert the date field?
yes it would be the reason. I just tried
SELECT CONVERT(DATETIME, '10-12-09',101)
and got the same error. Can you make those 4 digit years?
SELECT CONVERT(DATETIME, '10-12-09',101)
and got the same error. Can you make those 4 digit years?
Bill,
what are the ranges of those 2 digit years? If they are 1950-2049 then you can just remove the " ,101" from the convert like this and it will work turning those years into 1950-2049.
SELECT CONVERT(DATETIME, '10-12-09')
what are the ranges of those 2 digit years? If they are 1950-2049 then you can just remove the " ,101" from the convert like this and it will work turning those years into 1950-2049.
SELECT CONVERT(DATETIME, '10-12-09')
ASKER
Thanks Guys!! I ended up updating all the null values to '01/01/1900' and then altered the column to a date type and it worked!!...Thanks for all your help!!
one syntax problems though, the case will complain about non-boolean type for the condition so change the case to :
select Case
When IsDate(MyTextField)=1 Then Convert(DateTime,MyTextFie
Else '01-01-1900'
End as MyDateField