Month Year concatenate

I knew how to do in t-sql:

RIGHT('0' + CAST(month as varchar(2)) + '\' + cast(year) as varchar(4)), 7)

Select allOpen in new window

Use T-SQL transformation in the middle to involve this query:

CAST(Year as char(4)) + RIGHT('00' + CAST(Month as varchar(2)), 2)

Above T-SQL Transformation should help you out.

Angel Eye's is correct - http:#24738174, just has a type-o that you can easily fix by removing the extra parenthesis behind year.  Please accept his, just wanted to help.

RIGHT('0' + CAST([month] as varchar(2)) + '\' + cast([year] as varchar(4)), 7)

Select allOpen in new window

I just realised that i need the day too.....so the format i need is 01\01\2007 ,02\01\2008

by default the day is the First dy of the month..Thank you

Just amend Angel Eye's solution with a static/literal '01\'.

RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 7)

Select allOpen in new window

Sorry, you need to change the 7 at the end to 10.

RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 10)

Select allOpen in new window

       

RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 10).. I used this and i got the following errors

Error at Data Flow Task [Derived Column [16]]: Parsing the expression "         RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 10)" failed. The single quotation mark at line number "1", character number "16", was not expected.

Error at Data Flow Task [Derived Column [16]]: Cannot parse the expression "         RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 10)". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [16]]: The expression "         RIGHT('0' + CAST([month] as varchar(2)) + '\01\' + cast([year] as varchar(4)), 10)" on "output column "ReportDate" (145)" is not valid.

See if it is the \ needing to be escaped.

RIGHT('0' + CAST([month] as varchar(2)) + '\\01\\' + cast([year] as varchar(4)), 10)

Select allOpen in new window

Or try with forward slash:

RIGHT('0' + CAST([month] as varchar(2)) + '/01/' + cast([year] as varchar(4)), 10)

Select allOpen in new window

Nope, Still getting the same error.

Both Year And Month are declared as Varchar.

If they are already varchar, then you can simplify to this:

RIGHT('0' + month, 2) + '/01/' + @year

Select allOpen in new window

Solved!!