We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Month Year concatenate

Medium Priority
757 Views
Last Modified: 2013-11-10
Hi,

My table contains only the  Year and the MONTH. I want to get a new column StartDate in Month\Year Format. I am using derived column trans in SSIS.

I am stuck with the condition that i need to specify.

Year      Month     New Column StartDate
----         ------         ------------
2004       1                  01\2004
2004       2                  02\2004
2004       3                  03\2004
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I knew how to do in t-sql:
RIGHT('0' + CAST(month as varchar(2)) + '\' + cast(year) as varchar(4)), 7)

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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)

Open in new window

Author

Commented:
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
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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)

Open in new window

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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)

Open in new window

Author

Commented:
       

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.



Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
See if it is the \ needing to be escaped.
RIGHT('0' + CAST([month] as varchar(2)) + '\\01\\' + cast([year] as varchar(4)), 10)

Open in new window

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Or try with forward slash:
RIGHT('0' + CAST([month] as varchar(2)) + '/01/' + cast([year] as varchar(4)), 10)

Open in new window

Author

Commented:
Nope, Still getting the same error.

Author

Commented:
Both Year And Month are declared as Varchar.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
If they are already varchar, then you can simplify to this:
RIGHT('0' + month, 2) + '/01/' + @year

Open in new window

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Solved!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.