float to date SSIS

Question

float to date SSIS

joanna.seldon

Ten Centuries

Points: 1188
More actions
May 31, 2017 at 4:28 am

#338012

Hi
new to SSIS
in SQL a view I have successfully converted a float date "OrderYearMonth" that looks like 201601 to a date
note: just given a random day of 01
so it appears 2016/01/01, 2016/02/01 ect
however i am trying to do the same in SSIS, by using a derived column transformation
CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, OrderYearMonth* 100 + 1)))
It does not seem to work
please help

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

joanna.seldon - Wednesday, May 31, 2017 4:27 AM
Hi
new to SSIS
in SQL a view I have successfully converted a float date "OrderYearMonth" that looks like 201601 to a date
note: just given a random day of 01
so it appears 2016/01/01, 2016/02/01 ect
however i am trying to do the same in SSIS, by using a derived column transformation
CONVERT( DATE, CONVERT(VARCHAR(8), CONVERT(INT, OrderYearMonth* 100 + 1)))
It does not seem to work
please help

Simpler approach
😎

CONVERT(DATE,(CONVERT(VARCHAR(8),OrderYearMonth,0) + '01'),112)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

The problem is that you're coding in T-SQL and SSIS uses a different language for derived columns.
Here's a formula you can use:
(DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Luis Cazares - Wednesday, May 31, 2017 5:39 AM
The problem is that you're coding in T-SQL and SSIS uses a different language for derived columns.

He he, good point Luis, I missed the obvious
😎

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 4

hi

I am receiving an error with the code

cannot use DT_DBTIMESTAMP with a binary operator "/"

I have tried to change this to Database date [DT_DBDATE] with no joy

I have also tried to put a data conversion, before the derived column transformation , with no joy

I don't need the time , just the date, but time date would be good if easier

please help

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

joanna.seldon - Wednesday, May 31, 2017 6:54 AM
hi
I am receiving an error with the code
cannot use DT_DBTIMESTAMP with a binary operator "/"
I have tried to change this to Database date [DT_DBDATE] with no joy
I have also tried to put a data conversion, before the derived column transformation , with no joy
I don't need the time , just the date, but time date would be good if easier
please help

Please post the formula that you're using. The one that I posted is tested and worked fine.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 6

Hi

the source is an excel document

OrderYearMonth
201601
201501
201401

I am using then a data conversion for the column OrderYearMonth - DT_DBTIMESTAMP

the I am using the derived column transformation to place this code

(DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")

with no joy - with error DT_DBTIMESTAMP cannot be used with a binary operator "/"

please help

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

joanna.seldon - Wednesday, May 31, 2017 7:54 AM
Hi
the source is an excel document
OrderYearMonth
201601
201501
201401
I am using then a data conversion for the column OrderYearMonth - DT_DBTIMESTAMP
the I am using the derived column transformation to place this code
(DT_DBTIMESTAMP)((DT_WSTR,4)(OrderYearMonth / 100) + "-" + (DT_WSTR,2)(OrderYearMonth % 100) + "-01")
with no joy - with error DT_DBTIMESTAMP cannot be used with a binary operator "/"
please help

In the Advance Editor (right click on the source), Input and Output Properties tab. What data type is OrderYearMonth on the Output Columns?
If it's a float, then you need to convert the column to int before any operation.
(DT_DBTIMESTAMP)((DT_WSTR,4)((DT_I4)OrderYearMonth / 100) + "-" + (DT_WSTR,2)((DT_I4)OrderYearMonth % 100) + "-01")

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 8

Hi

the excel source column type is a float

and changing the file type to the int worked!

brill thanks team