September 10, 2008 at 4:30 am
as part of a data extract I would like to generate a unique key based upon the date and a job number, in the format YYYY-MM-DD-JobNo.
In a derived column I do the following:
(DT_STR,4,1252)YEAR(entry_dt) + "-" + (DT_STR,2,1252)MONTH(entry_dt) + "-" + (DT_STR,2,1252)DAY(entry_dt) + "-" + (DT_STR,4,1252)job_no
This works, but the day and month if ommit the leading zeros. So rather than getting, 2008-09-09, I get 2008-9-9.
I tried using the cast DT_U1, but got the same result and also, I can't then concatenate the values, presumably because it thinks I trying to add two values together?
Any suggestion on how to fix this:
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
September 10, 2008 at 5:29 am
(DT_STR,4,1252)YEAR(entry_dt) + "-" + RIGHT('00' + (DT_STR,2,1252)MONTH(entry_dt),2) + "-" + RIGHT('00' + (DT_STR,2,1252)DAY(entry_dt),2) + "-" + (DT_STR,4,1252)job_no
September 10, 2008 at 6:18 am
comes up in red, when I input it into the expression window
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
September 10, 2008 at 6:23 am
this last method is a good one but just to not forget to use double quote and not single quote in your expression
RIGH("00"...
September 10, 2008 at 6:33 am
Sorry, I did not test it.
Yes, I think it is the single quote issue.
Basically concatenate two zeros (as a string) to your number and then take the right 2 characters.
September 10, 2008 at 6:43 am
thanks - the single quotes were the issue
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply