Menu
How will you find last sunday of a month in sql 2000?
873
So, life is simple, you could use =A2-WEEKDAY(A2,11) to roll the Monday back to Sunday. This logic works throughout the week, all the way up through Saturday. The WEEKDAY(,11) of a Saturday is 6, so =A2-WEEKDAY(A2,11) will subtract 6 from the date and you end up on the prior Sunday.
The Excel WEEKDAY function takes a date and returns a number between 1-7 representing the day of week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. You can use the WEEKDAY function inside other formulas to check the day of week.
It does this my using EOMONTH to get the last day of the month, then adding one day: = EOMONTH ( B5 , 0 ) + 1 Next, the formula calculates the number of days... The DAY function returns the day value for a date.
SELECT
DATEADD(day,DATEDIFF(day,'19000107',DATEADD(month,DATEDIFF(MONTH,0,GETDATE() /*YourValuehere*/),30))/7*7,'19000107')
Edit: A correct, final, working answer from my colleague.
142
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
