Skip to Content
0

how to find last monday in a month using the date column

Feb 13 at 04:01 PM

123

avatar image

hi guys

Please help me to how to find the last Monday of month using the date column.

for eg:-

admission_dt = '2018-01-01'

last Monday dt should be ='2018-01-29'

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Dirk Venken
Feb 14 at 08:59 AM
0
last_date(admission_dt)-day_in_week(last_date(admission_dt))+1
Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Dirk

thanks for your reply... this works perfectly with last Monday but now business asking to change it to last Thursday..

I tried

last_date(admission_dt)-day_in_week(last_date(admission_dt))-3, it works fine for jan/feb dates but not working for sept date (2016-09-22 ) returning second last Thursday (2016-09-22) rather than (2016-09-29)

sorry for being trouble maker

regards

0

last_date(admission_dt)-day_in_week(last_date(admission_dt))+decode(day_in_week(last_date(admission_dt))>=4,4,-3)

0

Thanks Dirk, it worked perfectly

0
avatar image
Former Member Feb 16 at 11:00 AM
0

Hi Leo,

For SQL Script, you can use the below code.

SELECT ADD_DAYS(LAST_DAY('2017-07-16'),-DAYOFWEEK(LAST_DAY('2017-07-16'))+2) FROM DUMMY

Graphical CV: The screenshot are attached.


step1.jpg (102.8 kB)
step-2.jpg (83.8 kB)
step-3.jpg (115.2 kB)
step-4.jpg (45.2 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 16 at 02:58 PM
0

Hi Leo,

This code will work for you.

select case when DAYOFWEEK(LAST_DAY('2018-02-16'))>5 THEN ADD_DAYS(LAST_DAY('2018-02-16'),-DAYOFWEEK(LAST_DAY('2018-02-16'))+5) ELSE ADD_DAYS(LAST_DAY('2018-02-16'),-DAYOFWEEK(LAST_DAY('2018-02-16'))-2) END as Last_Thursday FROM DUMMY

Regards,

Shawon

Share
10 |10000 characters needed characters left characters exceeded
Sukanta Sengupta Feb 18 at 08:48 PM
0

Hi,

I am new to BODS, could you please help me with below query.

Basically I need job level information in metadata tables. So I am getting almost every details from metadata tables apart from schema name and system configuration details. So is their any way I can get schema name and system configuration details in metadata tables.

Thanks in advance...

SAP Data Services Answer Alert Moderator
Share
10 |10000 characters needed characters left characters exceeded
leo dec Feb 16 at 07:50 AM
0

Hi Dirk

thanks for your reply... this works perfectly with last Monday but now business asking to change it to last Thursday..

I tried

last_date(admission_dt)-day_in_week(last_date(admission_dt))-3, it works fine for jan/feb dates but not working for sept date (2016-09-22 ) returning second last Thursday (2016-09-22) rather than (2016-09-29)

sorry for being trouble maker

regards

Share
10 |10000 characters needed characters left characters exceeded
leo dec Feb 16 at 11:34 AM
0

hi Shawon

thanks for you reply.. to find last Monday in every month... formula given by Dirk is working perfectly fine...

but when I try to find last Thursday for every month, same formula is not working in data services

regards

Share
10 |10000 characters needed characters left characters exceeded