In my Derived Column Transformation Editor I have something like this:
DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])
where [Schedule] is a database timestamp field from a OLEDB Datasource.
I want to produce a string something like: "DD/MM/YYYY"
using the expression above, I get something really wierd like "1905-07-21 00:00:00"
Help much appreciated!
Hey Jhon,
DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.
If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:
(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])
keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.
Rafael Salas
|||Thanks!... I'll try it|||I'd like to add a couple of things to Rafael's suggestion.
First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.
Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:
RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)
Thanks
Mark
I ended up with this. Thanks for the great help!
RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)
|||One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:
RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)
