Returning the first date of each week

SQL++
1

I have date returning in select in one of the query as ‘yyyyy-mm-dd’ format .
I want to return the 1st day of that month for e.g. if ‘2020-01-12’ I want to return ‘2020-01-01’ …for which I used date_trunc_str which works fine . However I am not finding any solution or function which will return the first day of each week considering Sunday is beginning of the week …
So in select if I have ‘2020-Jan-8’ I would like to return … .‘2020-01-05’ i.e Sunday of this week and next week ‘2020-01-12’ and ‘2020-01-19’ so on so forth .
Any way easily to achieve this with builtin couchbase functions ?
thanks

2

There is no easy way to get that.
But you can use this

DATE_ADD_STR(d, 
{"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(d)],"day")


SELECT  DATE_ADD_STR(d, {"Sunday":0,"Monday":-1,"Tuesday":-2,"Wednesday":-3,"Thursday":-4,"Friday":-5,"Saturday":-6}.[WEEKDAY_STR(d)],"day") LET d = "2020-01-11";
{
    "results": [
    {
        "$1": "2020-01-05"
    }
    ]
}
1 Like
3

@vsr1 - Loved that solution . thanks again and happy new year :slight_smile: