Date format from Day to MonYy

[OscarBoots1]

Hi Forum,

I would like to get all of the dates in a given month by joining 2 tables together on their 'MonYr' field. 

Table A has a 'RptDate' field value of '16AUG17' format DATE7. and a 'MonYr' field derived from the RptDate field by changing the format to 'MONYY5.' so the 'MonYr' value is 'AUG17'. 

Table B also has a 'RptDate' field for all Dates in 2017 and a 'MonYr' field also derived from the RptDate field by changing the format to 'MONYY5.' 

When I run the below statement, I only get the result for '16AUG17' instead of all dates in AUG17.

I think this is due to my creation of the MonYr field by changing the 'RptDate' format.

Can anyone advise a solution to this?      

SELECT 
RptDate 
FROM Table1 A
INNER JOIN Table2 B
ON A. MonYr = B. MonYr 

[Jagadishkatam · Accepted solution]

I definitely agree with your reason, it is because of the monyr variable that you derived using the format monyy5. with this approach the appearnce of the variable monyr alone is changing but internally the monyr has the full numeric date.

To overcome this issue, please derive the monyr variable with put function something like below 

monyr=put(rptdate,monyy5.);

this will derive the character monyr variable and on this variable if you merge you will get the expected output. Please derive the same in both the datasets you are merging. 

Hope this informtion will help you. 

[OscarBoots1]

Thanks Jag, works perfectly.

[Astounding]

Worth a try, but needs to be tested to see if it actually works:

on put(a.rptdate, monyy5.) = put(b.rptdate, monyy5.)

If it works, you don't need to create any new variables (not even MonYr).

In fact, it looks like you aren't selecting any variables from B.  So it would probably be faster (and might work if the above doesn't work) to switch to a subquery:

Select RptDate from A

where put(RptDate, monyy5.) in

(select distinct put(RptDate, monyy5.) from B)

;

[OscarBoots1]

Thanks Astounding, much appreciated !