Solved

SQL 2005 importing text field data into a date field

Posted on 2009-04-14
13
222 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:bjennings
  • 6
  • 6
13 Comments
 
LVL 15

Accepted Solution

by:
MNelson831 earned 250 total points
ID: 24141577
Use a query when you import and then use IsDate,  Case, and Convert

select Case
    When IsDate(MyTextField) Then Convert(DateTime,MyTextField,101)
    Else '01-01-1900'
End as MyDateField,
MyOtherField1,
MyOtherField2,
MyOtherField3,
Etc
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24141989
Good idea NMelson831,
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,MyTextField,101)
    Else '01-01-1900'
End as MyDateField
0
 

Author Comment

by:bjennings
ID: 24142127
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?  

0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 total points
ID: 24143169
Put it in place of your textdate field like this
select field1, field2, Case
    When IsDate(textdate)=1 Then Convert(DateTime,textdate,101)
    Else '01/01/1900'
End as realdate, field4 from mydatabase
0
 

Author Comment

by:bjennings
ID: 24148176
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
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24148598
Yes, realdate was just a name I made up to give the value a column alias.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:bjennings
ID: 24149171
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
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24149510
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.
0
 

Author Comment

by:bjennings
ID: 24150305
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
0
 

Author Comment

by:bjennings
ID: 24151233
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?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24151740
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?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24151800
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')
0
 

Author Closing Comment

by:bjennings
ID: 31570107
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!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now