run today would return Sunday 1/29/23
a classic for your troubles
a classic for your troubles
Post Reply
1 of 1
Last Sunday of previous month SQL code
1,892 Views |
13 Replies |
Last: 2 yr ago by ABATTBQ11
Wood inject my SQL through her backdoor
ChatGPT says:
SELECT DATE_SUB(DATE_SUB(LAST_DAY(NOW() - INTERVAL 1 MONTH), INTERVAL WEEKDAY(LAST_DAY(NOW() - INTERVAL 1 MONTH)) DAY), INTERVAL 6 DAY);
I have not tested this...
SELECT DATE_SUB(DATE_SUB(LAST_DAY(NOW() - INTERVAL 1 MONTH), INTERVAL WEEKDAY(LAST_DAY(NOW() - INTERVAL 1 MONTH)) DAY), INTERVAL 6 DAY);
I have not tested this...
Some things never change around here.
I invented the rule in 2005. I'm not going to break it.
doesnt work in my dbeaver environment. i hate dates with sql. not as bad as business objects, but still ridiculous.
What database are you using?
data lake table for work.
I only have access to Oracle and Postgres:
Postgres:
select date_trunc('week', date_trunc('month', CURRENT_DATE) - interval '1 day') - interval '1 day';
Oracle:
select trunc(trunc(LAST_DAY(SYSDATE-INTERVAL '1' MONTH)), 'DAY') from dual;
Postgres:
select date_trunc('week', date_trunc('month', CURRENT_DATE) - interval '1 day') - interval '1 day';
Oracle:
select trunc(trunc(LAST_DAY(SYSDATE-INTERVAL '1' MONTH)), 'DAY') from dual;
Sql server? Oracle?
In SQL Server:
SELECT
DATEADD(DAY,-DATEPART(DW,EOMONTH(GETDATE(),-1))+1,EOMONTH(GETDATE(),-1))
Tested and returns 1/29/23
SELECT
DATEADD(DAY,-DATEPART(DW,EOMONTH(GETDATE(),-1))+1,EOMONTH(GETDATE(),-1))
Tested and returns 1/29/23
In Oracle:
SELECT
LAST_DAY(ADD_MONTHS(SYSDATE,-1)) - TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'D') + 1 FROM DUAL
Tested and returns 1/29/23
SELECT
LAST_DAY(ADD_MONTHS(SYSDATE,-1)) - TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'D') + 1 FROM DUAL
Tested and returns 1/29/23
Connecting to hive through presto
coworker gave me this
date_add('day', -1, date_trunc('week', (date_add('day', -1, date_trunc('month', current_date)))))
coworker gave me this
date_add('day', -1, date_trunc('week', (date_add('day', -1, date_trunc('month', current_date)))))
I think that does what you need, but I'm not super familiar with it. It's basically finding the first day of the month, then finding the first day of the week with that day in it if I'm reading it right. It would be much easier if TA had code blocks like stack exchange...