Board index » jbuilder » querying database for dates comparisons

querying database for dates comparisons


2005-02-28 11:21:42 PM
jbuilder9
HI ALL
I want to make a query
e.g To check dates for reminders(Alerting the user for specific task). Requirement is that query takes system current date and returns the result (containing range of dates which is 10 days next to current date) by searching in a cloumn of database table.
Column in database is varchar2 oracle database for Desktop application to manipulate swing components
Looking for reply.
 
 

Re:querying database for dates comparisons

"Lodhi" < XXXX@XXXXX.COM >wrote in message
Quote

HI ALL

I want to make a query
e.g To check dates for reminders(Alerting the user for specific task).
Requirement is that query takes system current date and returns the result
(containing range of dates which is 10 days next to current date) by
searching in a cloumn of database table.
Quote

Better to ask this in an Oracle group..
Hint: Stored Procedure would work best here.
 

Re:querying database for dates comparisons

"Lodhi" < XXXX@XXXXX.COM >wrote:
Quote

HI ALL

I want to make a query
e.g To check dates for reminders(Alerting the user for specific task). Requirement is that query takes system current date and returns the result (containing range of dates which is 10 days next to current date) by searching in a cloumn of database table.

Column in database is varchar2 oracle database for Desktop application to manipulate swing components
Looking for reply.
One of many ways to do this depending on the format of the date
within the varchar is :-
SELECT *
FROM myTable
WHERE to_date(myColumn,'DD-MON-RR') BETWEEN trunc(sysdate) and trunc(sysdate+9)
Hope this helps
Arth
 

{smallsort}

Re:querying database for dates comparisons

"Paul Nichols \(TeamB\)" < XXXX@XXXXX.COM >wrote:
Dear Paul,
Thanks for mail, I did not know what procedure to write, and i post this question on oracle newspost group but they suggested to try it on jbuilder group.. I also tried other way of date comparison (response recieved from TEAM B member ) but it also did not work. My date format is (Mar 1, 2005) stored as string in database.
Regards
Lodhi
Quote

"Lodhi" < XXXX@XXXXX.COM >wrote in message
news:42233706$ XXXX@XXXXX.COM ...
>
>HI ALL
>
>I want to make a query
>e.g To check dates for reminders(Alerting the user for specific task).
Requirement is that query takes system current date and returns the result
(containing range of dates which is 10 days next to current date) by
searching in a cloumn of database table.
>
Better to ask this in an Oracle group..

Hint: Stored Procedure would work best here.




 

Re:querying database for dates comparisons

Lodhi wrote:
Quote
Thanks for mail, I did not know what procedure to write, and i post
this question on oracle newspost group but they suggested to try it
on jbuilder group.. I also tried other way of date comparison
(response recieved from TEAM B member ) but it also did not work. My
date format is (Mar 1, 2005) stored as string in database.
Storing dates as strings is generally a bad idea. Oracle has a native
DATE type that happily handles all the necessary math for manipulating
dates and times (any decent introduction to Oracle book will tell you
more about dates than you ever wanted to know). Furthermore, if the
underlying database is using a DATE type, then Borland's Data Express
architecture will recognize it as such and create a column with the
appropriate type to map to the underlying date.
--
Kevin Dean [TeamB]
Dolphin Data Development Ltd.
www.datadevelopment.com/
NEW WHITEPAPERS
Team Development with JBuilder and Borland Enterprise Server
Securing Borland Enterprise Server
www.datadevelopment.com/papers/index.html
Please see Borland's newsgroup guidelines at
info.borland.com/newsgroups/guide.html
 

Re:querying database for dates comparisons

"Lodhi" < XXXX@XXXXX.COM >wrote:
Quote

"Paul Nichols \(TeamB\)" < XXXX@XXXXX.COM >wrote:

Dear Paul,

Thanks for mail, I did not know what procedure to write, and i post this question on oracle newspost group but they suggested to try it on jbuilder group.. I also tried other way of date comparison (response recieved from TEAM B member ) but it also did not work. My date format is (Mar 1, 2005) stored as string in database.

Regards
Lodhi
>
Now I know the date format try this
select * from myTable
WHERE to_date(myDateCol,'MON DD, YYYY') BETWEEN trunc(sysdate) and trunc(sysdate +9) ;
Arthur Ore
 

Re:querying database for dates comparisons

Bear in mind what Kevin said about it being better to store it
in the Oracle db as a date rather than a string. But if you have no control over the db design then what I posted will work.
Arthur Ore
 

Re:querying database for dates comparisons

Yes i agree with you to store date we have to use date datatype instead of string but problem is that i am using third party calendar date selection tool and i got selected date from that and when i try to save it throung jdbtextfield it did not take date in it so i first convert it in text from date and then store in database as string to retrive it i have to do process in reverse is ther any process to pass date in jdbtextfield without converting in string from let say thirdparty.getDate() method
Although query given by Arther Ore runs perfectly
"Kevin Dean [TeamB]" < XXXX@XXXXX.COM >wrote:
Quote
Lodhi wrote:

>Thanks for mail, I did not know what procedure to write, and i post
>this question on oracle newspost group but they suggested to try it
>on jbuilder group.. I also tried other way of date comparison
>(response recieved from TEAM B member ) but it also did not work. My
>date format is (Mar 1, 2005) stored as string in database.

Storing dates as strings is generally a bad idea. Oracle has a native
DATE type that happily handles all the necessary math for manipulating
dates and times (any decent introduction to Oracle book will tell you
more about dates than you ever wanted to know). Furthermore, if the
underlying database is using a DATE type, then Borland's Data Express
architecture will recognize it as such and create a column with the
appropriate type to map to the underlying date.

--
Kevin Dean [TeamB]
Dolphin Data Development Ltd.
www.datadevelopment.com/

NEW WHITEPAPERS
Team Development with JBuilder and Borland Enterprise Server
Securing Borland Enterprise Server
www.datadevelopment.com/papers/index.html

Please see Borland's newsgroup guidelines at
info.borland.com/newsgroups/guide.html
 

Re:querying database for dates comparisons

Well if you store it in the Oracle DB as a date you can convert it to a
String when you query it back.
SELECT to_char(myDateCol,'MON DD, YYYY') from myTable
WHERE myDateCol BETWEEN trunc(sysdate) and trunc(sysdate +9) ;
"Lodhi" < XXXX@XXXXX.COM >wrote in message
Quote

Yes i agree with you to store date we have to use date datatype instead of
string but problem is that i am using third party calendar date selection
tool and i got selected date from that and when i try to save it throung
jdbtextfield it did not take date in it so i first convert it in text from
date and then store in database as string to retrive it i have to do
process in reverse is ther any process to pass date in jdbtextfield
without converting in string from let say thirdparty.getDate() method
Although query given by Arther Ore runs perfectly

 

Re:querying database for dates comparisons

and you insert it as
INSERT INTO myTable VALUES (to_date('MAR 3, 2005','MON DD, YYYY'))