Monday/Sunday of date

Community Support
1

In SQL to get previous Monday of a date I do following:
DATEADD(day,(DATEDIFF(day,‘1900-01-01’,GETDATE())/7)*7,‘1900-01-01’)

To get following Sunday I do:

DATEADD(day,(DATEDIFF(day,‘1900-01-01’,GETDATE())/7)*7,‘1900-01-07’)

Note that this gives Monday/Sunday irrespective of system locale/settings (on some systems beginning of week would be Monday, on others Sunday).

I could just translate that to q to get the same result, but I am wondering if there is a more concise/nicer way of getting it in q.

2

since day is an int since 2000.01.01 (a saturday), you can use mod 7to get the offset since saturday.z.d - .z.d mod 7And from there + or - to any day of week you want.On Mon, Mar 2, 2015 at 12:46 PM, Greg Borota wrote:> In SQL to get previous Monday of a date I do following:> DATEADD(day,(DATEDIFF(day,‘1900-01-01’,GETDATE())/7)*7,‘1900-01-01’)>> To get following Sunday I do:> DATEADD(day,(DATEDIFF(day,‘1900-01-01’,GETDATE())/7)*7,‘1900-01-07’)>> Note that this gives Monday/Sunday irrespective of system locale/settings> (on some systems beginning of week would be Monday, on others Sunday).>> I could just translate that to q to get the same result, but I am wondering> if there is a more concise/nicer way of getting it in q.>>> –>

Submitted via Google Groups

3

Wow, this is beautiful.