Subtraction of date [message #7991] |
Mon, 21 July 2003 07:56 |
Anne
Messages: 36 Registered: April 2002
|
Member |
|
|
hie all, i want to be able to get data which dates back 4 weeks (28 days) from today's date. how can i do the command for this? does getting today's date means using the NOW() as in SQL Server? i have an idea on how this might turn out, but need some help wif it. my initial idea was to get today's date minus the date in each column and compared it with the number of days, inthis case, it is 28 days. is it what i am doing is correct?
Now() - To_Date("column_name", 'date format')<=28
Pls help n thanx in advance!
|
|
|
Re: Subtraction of date [message #7992 is a reply to message #7991] |
Mon, 21 July 2003 08:17 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Anne,
In SQL Server, I have no idea.
In Oracle:
Data setup:CREATE TABLE t (d DATE);
INSERT INTO t VALUES (TO_DATE('20030117','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030228','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030317','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030505','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030620','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030621','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030622','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030623','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030624','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030625','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030626','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030627','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030628','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030629','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030630','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030701','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030702','YYYYMMDD'));
INSERT INTO t VALUES (TO_DATE('20030703','YYYYMMDD'));
COMMIT; SELECT statement:SQL> SELECT TO_CHAR(SYSDATE
2 , 'fmMM/DD/YYYY') today
3 , RPAD(TO_CHAR(t.d
4 , 'fmMM/DD/YYYY'),19) "28_DAYS_OR_LESS_AGO"
5 , TRUNC(SYSDATE) - t.d difference
6 FROM t
7 WHERE (TRUNC(SYSDATE) - t.d) <= 28
8 ORDER BY t.d
9 /
TODAY 28_DAYS_OR_LESS_AGO DIFFERENCE
---------- ------------------- ----------
7/21/2003 6/23/2003 28
7/21/2003 6/24/2003 27
7/21/2003 6/25/2003 26
7/21/2003 6/26/2003 25
7/21/2003 6/27/2003 24
7/21/2003 6/28/2003 23
7/21/2003 6/29/2003 22
7/21/2003 6/30/2003 21
7/21/2003 7/1/2003 20
7/21/2003 7/2/2003 19
7/21/2003 7/3/2003 18
11 rows selected.
SQL> Note, the above will return future-dated rows: I don't know how that fact fits in with your business requirements or not.
Good luck, Anne.
A.
|
|
|