Solved

Month Year concatenate

Posted on 2009-06-29
14
599 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
0
Comment
Question by:Deepmath
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24738174
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

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24738179
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24738287
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

0
 

Author Comment

by:Deepmath
ID: 24738529
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
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24738537
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

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24738545
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

0
 

Author Comment

by:Deepmath
ID: 24739004
       

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.



0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24739071
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

0
 
LVL 59

Expert Comment

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

Open in new window

0
 

Author Comment

by:Deepmath
ID: 24739111
Nope, Still getting the same error.
0
 

Author Comment

by:Deepmath
ID: 24739980
Both Year And Month are declared as Varchar.
0
 
LVL 59

Expert Comment

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

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24740127
Sorry forgot to remove one of the @ symbols.
RIGHT('0' + month, 2) + '/01/' + year

Open in new window

0
 

Author Closing Comment

by:Deepmath
ID: 31598030
Solved!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now