Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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,
0
bar0822
Asked:
bar0822
  • 10
  • 2
  • 2
  • +1
1 Solution
 
bar0822Author Commented:
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.

0
 
BrandonGalderisiCommented:
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.
0
 
SharathData EngineerCommented:
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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Alpesh PatelAssistant ConsultantCommented:
In SQL Server use ntext or text for Memo field in Access. And Try to import it from
0
 
bar0822Author Commented:
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.  
0
 
bar0822Author Commented:
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.  
0
 
bar0822Author Commented:
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?
0
 
bar0822Author Commented:
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
0
 
bar0822Author Commented:
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?  
0
 
BrandonGalderisiCommented:
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).
0
 
SharathData EngineerCommented:
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

0
 
bar0822Author Commented:
hi, i tried the case statement suggested and still getting an error on this date field.  
0
 
bar0822Author Commented:
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.
0
 
bar0822Author Commented:
OK - solved my problem.  Created another column with text datatype and data length of 10 and updated this column with values from the datetime field.  Imported to sql table that had column as datetime from ssis with no problem.  I guess that it was okay to import from text to datetime as long as the time portion was not attached to the date.

0
 
bar0822Author Commented:
I WORKED AROUND THE PROBLEM BUT APPRECIATED THE RESPONSES GIVEN BY ALL
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now