Solved

failure on error - cannot convert to datetime from character

Posted on 2011-03-15
15
412 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 21

Expert Comment

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

Author Comment

by:bar0822
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:bar0822
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
hi, i tried the case statement suggested and still getting an error on this date field.  
0
 

Author Comment

by:bar0822
Comment Utility
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
Comment Utility
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
Comment Utility
I WORKED AROUND THE PROBLEM BUT APPRECIATED THE RESPONSES GIVEN BY ALL
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 46
Access left join query 5 30
2 Access tables, count verbiage used 6 19
Error when saving to sql table a '/' 5 17
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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