Link to home
Start Free TrialLog in
Avatar of bar0822
bar0822

asked on

failure on error - cannot convert to datetime from character

Please help.  I am importing from Access a table that was imported into access as memo - changed the field datatype to datetime - now trying to import this table into sql 2005 from ssis.  I set up a temp table to import and then to sql table - works fine into temp table but then fails when importing to sql table - both temp and sql table fields are datetime.  
need to get this done asap.  thanx,
Avatar of bar0822
bar0822

ASKER

I had to change the datatype in access back to text because when creating a table to export to sql - there were errors in converting.  sorry,  it is 1:30 a.m.and have been up since 5:30 a.m. - need some sleep. I need to get this table into Sql 2005 but getting errors.

If in Access you are using a text/memo field, then most likely someone put a date in using a format not consistent with other date values format.

Example:
01-11-2011 and 11-01-2011.  Both are valid formats as mm-dd-yyyy and/or dd-mm-yyyy and depending on where you ask the question you will get a different response.  However 11-20-2011 will not work in the UK region and 20-11-2011 will not work in the US region without specific date format codes and a convert statement.
Avatar of Sharath S
Can you import into a temp table with data type as varchar and post the non-dates? You can run this query to get the non-dates.

select date_col from your_table where isdate(date_col) = 0
In SQL Server use ntext or text for Memo field in Access. And Try to import it from
Avatar of bar0822

ASKER

I've set the columns to datetime in Access.  In ssis package, I used a sql task to create a temp table on sql and import this column (the data type in temp table is datetime for column).  When trying to insert data from temp table on sql to the Main table (datetime on column) fails on converting datetime from character string.  why was it able to import to temp table and not main table?

here is what I am using to insert into sql main table from temp table and still fails.
case when ISDATE(DATE)= 1
        THEN CONVERT(datetime, date, 101)
         ELSE NULL END as

I used the isdate(mycol)=0 on the temp table created in sql and retrieved 10 nulls - is nulls the problem? if so, how do I correct this?

I was able to import the table from Access to SQL using wizard and imported ok but I need to use ssis or a sql script.  
Avatar of bar0822

ASKER

Also, here is how the dates are being imported into  
Access:  yyyy-mm-dd 00:00:00  when I append to a table with datetime field in Access, the date changes to mm/dd/yyyy.  
Avatar of bar0822

ASKER

Please have to have this completed by today.
I changed the insert statement on SQL to:
case when ISDATE(DATE)= 1
        THEN CONVERT(datetime, DATE)
         ELSE '1/1/1900' END as
DATE

it worked for one date column but not for the other - message failed converting datetime from string.  Why does it work for one date and not other?
Avatar of bar0822

ASKER

worked for one date formatted as 2010-11-04 00:00:00.000 in access

but the other date is formatted this way: 2010-11-23 09:48:28.000  in access.  Is there some way to convert this as the first date formatted as 2010-11-04 00:00:00.000?  

Please help - I am new at this and need to get this working asap!  thanx
Avatar of bar0822

ASKER

I changed the formatting of the date in access to short date 1/1/2011 and I opened the table and dates are formatted as 1/1/2011 - but when importing into the temp table on SQL it is still coming in from Access to SQL with date and time format   e.g. 11/10/2010 12:46:13 PM?
can anyone tell me what I am doing wrong here?  
Unfortunately, isdate() isn't going to cut it.  I would recommend changing your process so that instead of bulking the entire table in, bulk in batches of 100-1000 at a time.  In the end, it will help you to identify which is the offending record(s).
Can you try changing your CASE statement like this.
case when ISDATE([DATE])= 1
        THEN CONVERT(datetime, [DATE])
         ELSE CONVERT(datetime,'1/1/1900') END as 
[DATE]

Open in new window

Avatar of bar0822

ASKER

hi, i tried the case statement suggested and still getting an error on this date field.  
Avatar of bar0822

ASKER

date is stored in access as 09/12/2007 3:36:59 PM - the date was imported into access as a memo field.  Changed the datatype to datetime in access but still showing as above and when trying to import into sql table datetime using ssis package - fails on cannot convert to datetime from character.
ASKER CERTIFIED SOLUTION
Avatar of bar0822
bar0822

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bar0822

ASKER

I WORKED AROUND THE PROBLEM BUT APPRECIATED THE RESPONSES GIVEN BY ALL