Home » SQL & PL/SQL » SQL & PL/SQL » Subtraction of date
Subtraction of date [message #7991] Mon, 21 July 2003 07:56 Go to next message
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 Go to previous message
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.
Previous Topic: String Parsing
Next Topic: selecting "set values" as rows without a table ...
Goto Forum:
  


Current Time: Thu Mar 28 09:30:40 CDT 2024