Desktop productivity for business analysts and programmers

Date format from Day to MonYy

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Date format from Day to MonYy

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 

Accepted Solutions
Solution
‎08-20-2017 06:21 PM
Trusted Advisor
Posts: 1,137

Re: Date format from Day to MonYy

Posted in reply to OscarBoots1

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. 

Thanks,
Jag

View solution in original post


All Replies
Solution
‎08-20-2017 06:21 PM
Trusted Advisor
Posts: 1,137

Re: Date format from Day to MonYy

Posted in reply to OscarBoots1

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. 

Thanks,
Jag
Contributor
Posts: 72

Re: Date format from Day to MonYy

Posted in reply to Jagadishkatam

Thanks Jag, works perfectly.

Super User
Posts: 5,610

Re: Date format from Day to MonYy

[ Edited ]
Posted in reply to OscarBoots1

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)

;

Contributor
Posts: 72

Re: Date format from Day to MonYy

Posted in reply to Astounding

Thanks Astounding, much appreciated !

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 223 views
  • 0 likes
  • 3 in conversation