CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlssis
Alex Gordon
Alex Gordon

Reputation: 60691

What format is this date and how can I convert it to a regular datetime?

Amazon for sellers provides order reports. I'm trying to import one of these order reports into a Sql Server database:

enter image description here

Their date fields look like this:

2014-04-30T12:17:28-07:00
2014-04-30T12:24:43-07:00
2014-04-30T12:25:34-07:00
2014-04-30T12:46:02-07:00
2014-07-27T13:10:02-07:00
2014-07-27T13:12:09-07:00
2014-07-27T13:20:42-07:00
2014-07-27T13:23:25-07:00
2014-07-27T13:29:10-07:00
2014-07-27T13:36:16-07:00
2014-07-27T13:51:41-07:00

I cannot figure out which data type to assign this date.

How do I convert this field to be a regular datetime? The solution could be SQL or SSIS or a combination.

Upvotes: 0

Views: 524

Answers (1)

M.Ali
M.Ali

Reputation: 69514

Try this.....

SQL Server

SELECT  CONVERT(DATETIME,
                  CONVERT(DATETIME2, '2014-04-30T12:17:28-07:00')
                  )

RESULT: 2014-04-30 12:17:28.000   --<-- SQL SERVER DATETIME

SSIS

  1. Convert your input column to SSIS Data type DT_DBTIMESTAMP2

  2. Add a derived column task and use the following expression 

    (DT_DBTIMESTAMP)Input_Column_Name

essentially you are doing the same thing but result will be the same.

Upvotes: 1

Related Questions