How to convert 12312007 into 12/31/2007

Question

How to convert 12312007 into 12/31/2007

jsheldon

SSCertifiable

Points: 5250
More actions
January 29, 2008 at 12:42 pm

#123405

Absolutely stumped on this...I am a bit of the novice in SSIS. I have a flat file where the date is formatted as 12312007.
In my source flat file connection I have a data type of DATE (DT_DATE)
In my destination SQL Server Table the field is datetime
Thus I get an error on an unallowed convert.
What I truly would like is a conversion of 12312007 to 12/31/2007 12:00:00AM if that is possible then I wouldn't have to change the column type on the SQL table.
If this cannot be done then I can change the column on the SQL table as a last resort.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

jsheldon (1/29/2008)
Absolutely stumped on this...I am a bit of the novice in SSIS. I have a flat file where the date is formatted as 12312007.
In my source flat file connection I have a data type of DATE (DT_DATE)
In my destination SQL Server Table the field is datetime
Thus I get an error on an unallowed convert.
What I truly would like is a conversion of 12312007 to 12/31/2007 12:00:00AM if that is possible then I wouldn't have to change the column type on the SQL table.
If this cannot be done then I can change the column on the SQL table as a last resort.
Thanks

You can convert to an outbound date in a derived column transformation, something like:

LEN(SomeDate) == 0 ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(SomeDate,1,2) + "/" + SUBSTRING(SomeDate,3,2) + "/" + SUBSTRING(SomeDate,5,4))

This assumes that you want NULL for an empty date, as opposed to a tokenized unknown.

In the cases where I am reading an MMDDCCYY date in from a flat file, I use a DT_STR string type for the inbound datatype, and convert them in the derived column.

jsheldon SSCertifiable Points: 5250 More actions · Answer 2

D smith,

questions:

If the values coming in from the flat file do not contain blanks at all, can I bypass the NULL reference? Also in Derived Column what do you have as the Datatype?

I switched the original clearance_date type in the flat file from DT_DATE to DT_STR

Here is what I have:

Derived Column

adj_clearance_data

Derived Column

Expression

(DT_DATE)(SUBSTRING(clearance_date,1,2) + "/" + SUBSTRING(clearance_date,3,2) + "/" + SUBSTRING(clearance_date,5,4))

Data Type

date[DT_DATE]

Length, Precision, Scale and Code Page default in as blank

Dave23 Hall of Fame Points: 3903 More actions · Answer 3

jsheldon (1/29/2008)
D smith,
questions:
If the values coming in from the flat file do not contain blanks at all, can I bypass the NULL reference? Also in Derived Column what do you have as the Datatype?
I switched the original clearance_date type in the flat file from DT_DATE to DT_STR
Here is what I have:
Derived Column
adj_clearance_data
Derived Column
Expression
(DT_DATE)(SUBSTRING(clearance_date,1,2) + "/" + SUBSTRING(clearance_date,3,2) + "/" + SUBSTRING(clearance_date,5,4))
Data Type
date[DT_DATE]
Length, Precision, Scale and Code Page default in as blank

If there are definately no empty dates inbound, then you can skip the NULL check. I have the data type in the derived column set to DT_DATE, and Length, Precision, Scale and Code Page default in as blank as well.