Solved

failure on error - cannot convert to datetime from character

Posted on 2011-03-15
15
417 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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