Newbe is having problems with importing dates from Excel

[roaml]

Hello,

I am trying to import a spreadsheet from Excel to a SQL (2000) table. All data seem to import fine except for the dates. I receive the following message:

Conversion invalid for datatypes (source column 'date_required' (DBTYPE_WSTR), destination column 'date_required' (DBTYPE_DBTIMESTAMP)

Hopefully someone will be able to help. I am a newbe to SQL and this is very frustrating!

Thank you.

 

[SQLSister]

What is the data type for your date field?

 

[roaml]

I have tried every data type possible. I even customed my own data type (mm/dd/yyyy hh:mm:ss AM/PM)and I still receive the same errors.

Any sugestions?

Thanks.

 

[homebrew01]

Could be that you have invalid data in your Excel file, such as a month GT 12, or day GT 31 ....... SQL verifies that your data is "legal" if it's going into a date field. Maybe if you first import into a work table with a character field, then you can do clean-up, then DTS to your live table

 

[roccorocks]

I made a file an excel spreadsheet and succesfully imported all the data to the table. I am using the standard DATETIME format on the date field in the DB.

In the spreadsheet, if I click on the date field this is shown '1/3/1900 11:59 AM', however, when just "Looking" at the field in Excel it shows this '1/3/1900 11:59:56'. Notice no AM/PM stuff. Excel is flakey sometimes, I deal with it a lot. I will mess with it a little to see if I can reproduce what you are experiencing.

The formatting in my spreadsheet is: m/d/yyyy h:mm.

26 FName LName '1/3/1900 11:59:56 AM'

Rocco

 

[roaml]

Thank you homebrew01 and roccorocks for your suggestions. I will take a closer look at the dates and try your formatting suggestions.

I have been working so long on this and cannot figure out what I'm doing wrong. So frustrating! %-(

Thanks again for your help.