Concatenate using an IF/Else statement? 1

[ordendelfai]

I am a using Access 97 and trying to use a pass through query to a SQL Server, but have never used IF statements with T-SQL before, so haven't a clue what I am doing.

I have a date field that contains dates in the format "1/1/2004", and I needs to create a custom column that takes those values and converts them to "2004/01".

I have been able to get a column that displays "2004/1", but that will not work because the trailing zero before the 1 is missing. I could easily add a zero, but then October and higher would be "010". I was hoping an if statement would work, but the code I am trying to make work below doesn't compile. If someone could fix the code so the IF statement works, I would greatly appreciate it!!! ;-)

SELECT tbl_CmpnyData_Main.PHID, tbl_CmpnyData_Main.Company_Name, tbl_CmpnyData_Main.Renewal_Date AS MainRenewal, tbl_CmpnyData_Main.Prospect, tbl_CmpnyData_Main.PBGH, [First_Name] + ' ' + [Last_Name] AS UnderwriterName, tbl_CmpnyData_Main.AE_ID, tbl_CmpnyData_Main.AM_ID, tbl_CmpnyData_Main.MR_ID, tbl_CmpnyData_Main.Total_Eligible, tbl_CmpnyData_Main.CA_Eligible, tbl_CmpnyData_Main.Status_Date, tbl_Renewals.Renewal_Date AS Ren_Renewal, tbl_Renewals.Est_LossGain, tbl_RiskOpps.Type, tbl_RiskOpps.Renewal_Date AS Risk_Renewal, tbl_RiskOpps.RiskOppRating, tbl_RiskOpps.MCR_Date1_1, tbl_RiskOpps.MCR_Date1_2, tbl_RiskOpps.MCR1, tbl_RiskOpps.MCR_Date2_1, tbl_RiskOpps.MCR_Date2_2, tbl_RiskOpps.MCR2, tbl_RiskOpps.Last_Update, tbl_RiskOpps.twoYearMCR, tbl_RFP_RFI.DateRcvd, tbl_RFP_RFI.DateDue, tbl_RFP_RFI.DateReleased, tbl_RFP_RFI.ProposalWriter, tbl_RFP_RFI.Probability, tbl_RFP_RFI.Status, tbl_RFP_RFI.SoldRates, tbl_RFP_RFI.Winner, tbl_RFP_RFI.Notes, tbl_RFP_RFI.Last_Updated, CAST(DatePart(year, [tbl_Renewals].[Renewal_Date]) as Char(4)) AS mYear,

[COLOR=red yellow]
CAST(DatePart(year, [tbl_Renewals].[Renewal_Date]) as Char(4)) + '/' + 

IF Select DatePart(Month, [tbl_Renewals].[Renewal_Date]) From [tbl_Renewals].[Renewal_Date]
Where DatePart(Month, [tbl_Renewals].[Renewal_Date]) From [tbl_Renewals].[Renewal_Date] < 10 
Begin
 '0' + CAST(DatePart(Month, [tbl_Renewals].[Renewal_Date]) as Char(1))  AS YearMonth
Else
CAST(DatePart(Month, [tbl_Renewals].[Renewal_Date]) as Char(2))  AS YearMonth
[/color] 
FROM (((tbl_CmpnyData_Main LEFT JOIN tbl_Renewals ON tbl_CmpnyData_Main.PHID = tbl_Renewals.PHID) LEFT JOIN tbl_RFP_RFI ON tbl_Renewals.PHID = tbl_RFP_RFI.PHID) LEFT JOIN tbl_RiskOpps ON tbl_Renewals.PHID = tbl_RiskOpps.PHID) LEFT JOIN tbl_Underwriters ON tbl_CmpnyData_Main.Underwriter = tbl_Underwriters.[ID#]
ORDER BY tbl_CmpnyData_Main.PHID;

~Joel

 

[ESquared]

You might find some luck with the CASE statement. Look it up in SQL Server Books Online

IF THEN ELSE
->
CASE WHEN THEN ELSE END

 

[ordendelfai]

Thank you so much, that function was a lot easier and worked perfectly!!!!

SELECT tbl_CmpnyData_Main.PHID, tbl_CmpnyData_Main.Company_Name, tbl_CmpnyData_Main.Renewal_Date AS MainRenewal, tbl_CmpnyData_Main.Prospect, tbl_CmpnyData_Main.PBGH, [First_Name] + ' ' + [Last_Name] AS UnderwriterName, tbl_CmpnyData_Main.AE_ID, tbl_CmpnyData_Main.AM_ID, tbl_CmpnyData_Main.MR_ID, tbl_CmpnyData_Main.Total_Eligible, tbl_CmpnyData_Main.CA_Eligible, tbl_CmpnyData_Main.Status_Date, tbl_Renewals.Renewal_Date AS Ren_Renewal, tbl_Renewals.Est_LossGain, tbl_RiskOpps.Type, tbl_RiskOpps.Renewal_Date AS Risk_Renewal, tbl_RiskOpps.RiskOppRating, tbl_RiskOpps.MCR_Date1_1, tbl_RiskOpps.MCR_Date1_2, tbl_RiskOpps.MCR1, tbl_RiskOpps.MCR_Date2_1, tbl_RiskOpps.MCR_Date2_2, tbl_RiskOpps.MCR2, tbl_RiskOpps.Last_Update, tbl_RiskOpps.twoYearMCR, tbl_RFP_RFI.DateRcvd, tbl_RFP_RFI.DateDue, tbl_RFP_RFI.DateReleased, tbl_RFP_RFI.ProposalWriter, tbl_RFP_RFI.Probability, tbl_RFP_RFI.Status, tbl_RFP_RFI.SoldRates, tbl_RFP_RFI.Winner, tbl_RFP_RFI.Notes, tbl_RFP_RFI.Last_Updated, CAST(DatePart(year, [tbl_Renewals].[Renewal_Date]) as Char(4)) AS mYear,
[COLOR=green]
'YearMonth' = 
CASE 
    WHEN [tbl_CmpnyData_Main].[Renewal_Date] < 10 THEN CAST(DatePart(year, [tbl_Renewals].[Renewal_Date]) as Char(4)) + '/' + 
'0' + CAST(DatePart(Month, [tbl_Renewals].[Renewal_Date]) as Char(1))
    WHEN [tbl_CmpnyData_Main].[Renewal_Date] > 9 THEN CAST(DatePart(year, [tbl_Renewals].[Renewal_Date]) as Char(4)) + '/' + 
CAST(DatePart(Month, [tbl_Renewals].[Renewal_Date]) as Char(2))
End
[/color]
FROM (((tbl_CmpnyData_Main LEFT JOIN tbl_Renewals ON tbl_CmpnyData_Main.PHID = tbl_Renewals.PHID) LEFT JOIN tbl_RFP_RFI ON tbl_Renewals.PHID = tbl_RFP_RFI.PHID) LEFT JOIN tbl_RiskOpps ON tbl_Renewals.PHID = tbl_RiskOpps.PHID) LEFT JOIN tbl_Underwriters ON tbl_CmpnyData_Main.Underwriter = tbl_Underwriters.[ID#]
ORDER BY tbl_CmpnyData_Main.PHID;