[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

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?
0
fwstealer
Asked:
fwstealer
  • 2
1 Solution
 
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.
0
 
Scott PletcherSenior 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.
0
 
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?
0
 
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)?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now