sql data type for date time stamp

I've got a csv file that has date/time represented in two columns as:

entrydate: 2012-04-06 11:12:40.000
shipdate: 4/7/2012 12:20:31 PM

I've got my sql table set up as:
[entrydate] [datetime] NULL,
[shipdate] [datetime] NULL,

when i try to import there is a conversion failure on the shipdate. so i switched the data type to a varchar 100 and it loads. What is the better data type for the shipdate column? Should i keep it at a varchar?
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
Use a staging table for the load from the csv, formatted as varchar.  [Even if you change the spreadsheet, it's still best to use a staging table when loading external data.]

Then when you move data from the staging table to the real/production table, change it to datetime, which is the proper datatype for this type of data.
Ken ButtersCommented:
I would open the CSV file in Excel... and refomat the ship date into the same format as your entry date... and keep the shipdate as a datetime.

Or --- even easier... wherever you got the CSV file from... that process should be able to provide you with a consistent date/time format.
fwstealerAuthor Commented:
wouldn't it be better to cast the shipdate in the ssis package and load directly to the table? if so how should i do the cast?
Scott PletcherSenior DBACommented:
Not for me; I would never use an external file to load directly into a production table.

But if generally you're willing to do that, then it depends.  If you have an invalid date, do you want the SSIS package to fail, or do you want to continue processing the valid data and just report on the invalid row(s)?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.