Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...