Home » SQL & PL/SQL » SQL & PL/SQL » to get values between date
to get values between date [message #207038] Mon, 04 December 2006 00:15 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello orafaq

i have several tables on my data base. i would like to get tables between the specific date. should i get those table with the help of SQL.


ashish>select * from user_objects
  2  order by created
  3  ;

CLIENT
                                    32329          32329 TABLE              03-OCT-06 03-OCT-06
2006-10-03:22:28:23 VALID   N N N

CLIENT_MASTER
                                    32347          32347 TABLE              04-OCT-06 12-OCT-06
2006-10-12:10:20:36 VALID   N N N

SYS_C002760
                                    32348          32348 INDEX              04-OCT-06 04-OCT-06
2006-10-04:23:33:43 VALID   N Y N

RAMU
                                    32357          32357 TABLE              05-OCT-06 17-NOV-06
2006-10-19:18:57:02 VALID   N N N

PRODUCT_MASTER
                                    32359          32359 TABLE              05-OCT-06 05-OCT-06
2006-10-05:21:19:25 VALID   N N N

SYS_C002769
                                    32360          32360 INDEX              05-OCT-06 05-OCT-06
2006-10-05:18:30:52 VALID   N Y N

SALESMAN_MASTER
                                    32367          32367 TABLE              05-OCT-06 05-OCT-06
2006-10-05:21:40:42 VALID   N N N

SYS_C002776
                                    32368          32368 INDEX              05-OCT-06 05-OCT-06
2006-10-05:21:33:52 VALID   N Y N

SALES_ORDER

OBJECT_NAME
----------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_
------------------------------ ---------- -------------- ------------------ --------- ---------
TIMESTAMP           STATUS  T G S
------------------- ------- - - -
                                    32389          32389 TABLE              06-OCT-06 06-OCT-06
2006-10-06:15:53:10 VALID   N N N

SYS_C002780
                                    32390          32390 INDEX              06-OCT-06 06-OCT-06
2006-10-06:15:53:11 VALID   N Y N

SALES_ORDER_DETAILS
                                    32391          32391 TABLE              06-OCT-06 06-OCT-06
2006-10-06:16:39:12 VALID   N N N

RAJU
                                    32417          32417 TABLE              12-OCT-06 04-DEC-06
2006-11-10:02:55:59 VALID   N N N

EMP1
                                    32427          32427 TABLE              12-OCT-06 04-DEC-06
2006-11-29:06:35:41 VALID   N N N

EMP2
                                    32437          32437 TABLE              15-OCT-06 15-OCT-06
2006-10-15:10:13:44 VALID   N N N

SYS_C002795
                                    32458          32458 INDEX              27-OCT-06 27-OCT-06
2006-10-27:20:50:30 VALID   N Y N

AUTHOR
                                    32467          32467 TABLE              31-OCT-06 31-OCT-06
2006-10-31:15:51:02 VALID   N N N

RATING
                                    32468          32468 TABLE              31-OCT-06 31-OCT-06
2006-10-31:16:18:56 VALID   N N N

CATEGORY
                                    32477          32477 TABLE              31-OCT-06 31-OCT-06
2006-10-31:17:33:56 VALID   N N N

BOOKSHELF_AUTHOR
                                    32478          32478 TABLE              31-OCT-06 31-OCT-06
2006-10-31:17:41:39 VALID   N N N

BOOKSHELF
                                    32483          32483 TABLE              31-OCT-06 31-OCT-06
2006-10-31:18:28:19 VALID   N N N

NEWSPAPER
                                    32488          32488 TABLE              31-OCT-06 31-OCT-06
2006-10-31:21:41:21 VALID   N N N

WEATHER
                                    32489          32489 TABLE              31-OCT-06 31-OCT-06
2006-10-31:22:30:25 VALID   N N N

LOCATION
                                    32490          32490 TABLE              31-OCT-06 31-OCT-06
2006-10-31:22:49:39 VALID   N N N

INVASION
                                    32491                VIEW               31-OCT-06 31-OCT-06
2006-10-31:23:10:25 VALID   N N N

ADDRESS_TY
                                    32493                TYPE               31-OCT-06 31-OCT-06
2006-10-31:23:35:20 VALID   N N N

PERSON_TY
                                    32494                TYPE               31-OCT-06 31-OCT-06
2006-10-31:23:43:43 VALID   N N N

CUSTOMER
                                    32495          32495 TABLE              31-OCT-06 01-NOV-06
2006-10-31:23:51:42 VALID   N N N

SYS_C002796
                                    32496          32496 INDEX              01-NOV-06 01-NOV-06
2006-11-01:00:17:26 VALID   N Y N

CIRCLE_AREA
                                    32519          32519 TABLE              11-NOV-06 11-NOV-06
2006-11-11:06:05:44 VALID   N N N

ACCOUNTS
                                    32527          32527 TABLE              11-NOV-06 11-NOV-06
2006-11-11:17:34:29 VALID   N N N

SYS_C002797
                                    32537          32537 INDEX              17-NOV-06 17-NOV-06
2006-11-17:17:31:24 VALID   N Y N

SUBJECT
                                    32559          32559 TABLE              19-NOV-06 19-NOV-06



i had done my query in such a way.

ashish>ed
Wrote file afiedt.buf

1 select * from user_objects
2 where created IN (
3 to_char(to_date('&date1', 'mon-yyyy'), 'mm-yyyy'),
4 to_char(to_date('&date2', 'mon-yyyy'), 'mm-yyyy')
5* )
ashish>/
Enter value for date1: oct-2006
old 3: to_char(to_date('&date1', 'mon-yyyy'), 'mm-yyyy'),
new 3: to_char(to_date('oct-2006', 'mon-yyyy'), 'mm-yyyy'),
Enter value for date2: sept-2006
old 4: to_char(to_date('&date2', 'mon-yyyy'), 'mm-yyyy')
new 4: to_char(to_date('sep-2006', 'mon-yyyy'), 'mm-yyyy')
to_char(to_date('sep-2006', 'mon-yyyy'), 'mm-yyyy'),
*
ERROR at line 3:
ORA-01843: not a valid month

what i had done wrong..
please help me out of this.

with regards
ashish
Re: to get values between date [message #207043 is a reply to message #207038] Mon, 04 December 2006 00:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If I look at the things you have been asking here lately, I strongly suggest you start reading in a good beginners guide for sql.
I don't mean to offend you by this, it is a well-meant advice. You don't seem to have a grip on the fundamentals of sql yet; without understanding the basics, you can never get to the more advanced features.

Having said that, here is where your query goes wrong:

user_objects.created is a column with datatype DATE. This means that if you want to compare the contents of this column to something else, you have to convert the 'something else' to a DATE as well.
You do convert users input to a date, using the to_date, but then you convert it back to a char (using to_char). Why? to_date('oct-2006') will generate a valid date. (representing october 1st 2006)

Furthermore, ranges in sql are depicted by 'between' not 'in'.

Finally, please follow posting-guidelines as stated in the stickies; 80 posts; you should know better Wink
Previous Topic: ANSI Joins
Next Topic: dynamic sql problem
Goto Forum:
  


Current Time: Tue Dec 06 04:24:16 CST 2016

Total time taken to generate the page: 0.07093 seconds