Solved

failure on error - cannot convert to datetime from character

Posted on 2011-03-15
15
416 Views
Last Modified: 2012-05-11
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
Comment
Question by:bar0822
  • 10
  • 2
  • 2
  • +1
15 Comments
 

Author Comment

by:bar0822
ID: 35144705
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35144875
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
 
LVL 40

Expert Comment

by:Sharath
ID: 35144926
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35145995
In SQL Server use ntext or text for Memo field in Access. And Try to import it from
0
 

Author Comment

by:bar0822
ID: 35147273
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
 

Author Comment

by:bar0822
ID: 35147298
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
 

Author Comment

by:bar0822
ID: 35147832
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
 

Author Comment

by:bar0822
ID: 35148509
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
 

Author Comment

by:bar0822
ID: 35149273
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35149608
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
 
LVL 40

Expert Comment

by:Sharath
ID: 35163104
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
 

Author Comment

by:bar0822
ID: 35227027
hi, i tried the case statement suggested and still getting an error on this date field.  
0
 

Author Comment

by:bar0822
ID: 35227046
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
 

Accepted Solution

by:
bar0822 earned 0 total points
ID: 35227290
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
 

Author Closing Comment

by:bar0822
ID: 35308381
I WORKED AROUND THE PROBLEM BUT APPRECIATED THE RESPONSES GIVEN BY ALL
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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