Skip to Content

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

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'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Feb 14 at 08:59 AM
    last_date(admission_dt)-day_in_week(last_date(admission_dt))+1
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 16 at 11:00 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 16 at 02:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 18 at 08:48 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 16 at 07:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 16 at 11:34 AM

    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

    Add comment
    10|10000 characters needed characters exceeded