OraFAQ Forum: Forms » comparing date problem

Home » Developer & Programmer » Forms » comparing date problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

comparing date problem [message #122138]

Fri, 03 June 2005 04:49

charlotte may
Messages: 8
Registered: June 2005

Junior Member

hi, help needed!

i dunno whether i ask this Qs under the correct section.
if not, pls forgive me.

i'm currently doing a project using vb.net.
my data type for the VALID_DATE is Date in oracle.

so now i want to retrieve all the data from oracle that the VALID_DATE is < today date.
so i use below statement in my select statement:-
VALID_DATE < TO_DATE (SYSDATE, 'dd/Mon/yyyy' )

is work (no error occur), but the datagrid does not display the record that the
VALID_DATE < today date.

NOTE:
the VALID_DATE that store in oracle database is as below:-

(format of the VALID_DATE in database: month / day / year )
VALID_DATE
5/19/2005
7/3/2005
6/21/2005

even i try this also cannot:-
VALID_DATE < TO_DATE (SYSDATE, 'dd/MM/yyyy' )

any help provider? it's urgent!
thanks a lot!

regards;
charlotte

Report message to a moderator

Re: comparing date problem [message #122314 is a reply to message #122138]

Sun, 05 June 2005 21:13

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Try the statement:

VALID_DATE < SYSDATE

The datatype of SYSDATE is date, so you don't have to do a TO_DATE on it. Assuming VALID_DATE also has a type of date, then you can compare them directly. In Oracle, a date is stored as a 7-digit number (I won't go into all the details) but basically, if you are comparing a date field against a date field you don't have to worry about whether the DISPLAY format is dd/mm or mm/dd.

If this has not answered your question please tell us the result you did receive from your attempt.

David

Report message to a moderator

Re: comparing date problem [message #122318 is a reply to message #122314]

Sun, 05 June 2005 21:38

charlotte may
Messages: 8
Registered: June 2005

Junior Member

thanks djmartin!

the data type for valid_date is Date in oracle database.
that's mean i comparing date field againts date field.

so i try to use valid_date < SYSDATE , BUT it still din't display the records i want (it runs, no error, just din't display the records i want.)!

may i know why?
thanks for the help!

it's urgent, hope hear from you soon. thanks!

regards;
charlotte

Report message to a moderator

Re: comparing date problem [message #122320 is a reply to message #122318]

Sun, 05 June 2005 21:45

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Please post the query that you have in the 'WHERE clause' of your block.

David

Report message to a moderator

Re: comparing date problem [message #122321 is a reply to message #122318]

Sun, 05 June 2005 22:09

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

From 'charlotte' via mail:

Quote:

Dim SQL As String = "SELECT a.APP_BADGE_NO, a.APP_ID, a.APP_NAME, a.APP_CAT, b.STFNAM, c.STFEXTNO FROM ESC_APPLICANT a, ESC_REQUESTOR b, CONTACT c WHERE a.STFNO = b.STFNO and a.STFNO = c.STFNO AND (a.STATUS = 'APPROVE' or a.STATUS = 'RENEW') and a.APP_VDATE_TO < SYSDATE "

Dim Cmd As New OleDbCommand(SQL, Conn)

Conn.Open()

DtReader = Cmd.ExecuteReader()

MyDataGrid.DataSource = DtReader

MyDataGrid.DataBind()

Conn.Close()

i even try a.APP_VDTAE_TO < trunc (SYSDATE). it still the same, no records display!

What I need you to do is use one of the other Oracle tools to test your SQL. Do you have access to TOAD or SQL*Plus?

Use either of these to run the SQL that you have in your code.

By the way, have your signed on to the database when you try to run this code?

David

[Updated on: Sun, 05 June 2005 22:10]

Report message to a moderator

Re: comparing date problem [message #122323 is a reply to message #122321]

Sun, 05 June 2005 22:21

charlotte may
Messages: 8
Registered: June 2005

Junior Member

thanks again!

i'm sorry that i 'm not familiar with oracle and i don't think that i have permission to use TOAD & SQL*plus(i think so).

may i know what do you mean by signed on?
do you mean connect to Oracle database?

yup, i hv!
i can store and retrive the data from oracle.
so, i also dunno wat is going wrong!

may i know does SYSDATE refer to today date?
Coz i wanna compare the APP_VDATE_TO < today date!

any other ideas?

thanks!

Report message to a moderator

Re: comparing date problem [message #122324 is a reply to message #122323]

Sun, 05 June 2005 22:34

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

It really is nearly useless to have you retrieving data from an Oracle database and not having an Oracle retrieval tool with which to test. Ask your manager for SQL*PLus , at least.

Do you have 'access' on your pc? Use the odbc facility to connect to the Oracle database and test your SQL.

I feel that your join "a.STFNO = b.STFNO and a.STFNO = c.STFNO" may be the problem. Do you have data in each of the tables for at least one STFNO? Is the a.STATUS field multi-character, or are 'APPROVE' and 'RENEW' expansions of a one character code 'A' and 'R'?

David

Report message to a moderator

Re: comparing date problem [message #122326 is a reply to message #122324]

Sun, 05 June 2005 22:50

charlotte may
Messages: 8
Registered: June 2005

Junior Member

thanks!

ya, i can connect to the oracle database!

and i'm sure that i have at least 1 data for the STFNO.
for the STATUS, it store as APPROVE and RENEW in oracle!

before that when i'm using MS Access as the database and CDate as a function to compare date!
it don't have any problem!
it work and display data!

but the records doesn't display when i use Oracle as database and i cant use the CDate function anymore!

thanks again!

regards;
charlotte!

Report message to a moderator

Re: comparing date problem [message #122327 is a reply to message #122326]

Sun, 05 June 2005 23:00

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

The questions that come to mind are: Are any of your fields NULL? Review your data using Access and verify that your DO have a record in each of the three tables that has the same STFNO, and that the APP_VDATE_TO is not NULL, and is less than today's date. If the APP_VDATE_TO is NULL does Access gives a 'true' to the statement that APP_VDATE_TO is less than SYSDATE?

Maybe you need some NVL constructs to resolve your query.

David

Report message to a moderator

Re: comparing date problem [message #122330 is a reply to message #122326]

Sun, 05 June 2005 23:18

charlotte may
Messages: 8
Registered: June 2005

Junior Member

hi, tx!

i check already!
the STFNO and APP_VDATE_TO is not null.
and it contain 1 record that the APP_VDATE_TO is less than today date!

in MS Access, if the APP_VDATE_TO is null, then it wouldn't generate the datagrid!
it will display the error say that the db is null!
it only will display the record if the APP_VDATE_TO & OTHERS SELECTED fields is not null.

did my query have some problem?
may i know what do you mean by "NVL constructs to resolve your query". how to do it?

regards;
charlotte

Report message to a moderator

Re: comparing date problem [message #122331 is a reply to message #122330]

Sun, 05 June 2005 23:59

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

I am afraid that I am missing something very obvious, but I can't see it.

Quote:

Dim SQL As String = "SELECT a.APP_BADGE_NO, a.APP_ID, a.APP_NAME, a.APP_CAT, b.STFNAM, c.STFEXTNO FROM ESC_APPLICANT a, ESC_REQUESTOR b, CONTACT c WHERE a.STFNO = b.STFNO and a.STFNO = c.STFNO AND (a.STATUS = 'APPROVE' or a.STATUS = 'RENEW') and a.APP_VDATE_TO < SYSDATE "

I can't see anything wrong in this SQL. You do have it on one line, right?

[Update:]This string is 251 characters. Is there a maximum length associated with strings? Can you define your string to be longer?

All I can suggest is that you start with a more basic SQL statment, like "SELECT a.APP_BADGE_NO, a.APP_ID, a.APP_NAME, a.APP_CAT FROM ESC_APPLICANT a"
and then add "WHERE (a.STATUS = 'APPROVE' or a.STATUS = 'RENEW')", then add "and a.APP_VDATE_TO < SYSDATE", etc.

David

[Updated on: Mon, 06 June 2005 00:00]

Report message to a moderator

Re: comparing date problem [message #122335 is a reply to message #122330]

Mon, 06 June 2005 00:19

charlotte may
Messages: 8
Registered: June 2005

Junior Member

hi, david!

i get an help from the staff here n he seem solve my problem already!

now i wanna select all the records for January so how am i going to write the sql statement?

Select * from ESC_APPLICANT where (ISSUE_DATE is january)?
sorry for keep on trouble you!

regards;
charlotte

Report message to a moderator

Re: comparing date problem [message #122338 is a reply to message #122335]

Mon, 06 June 2005 00:24

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

What was the solution?

Report message to a moderator

Re: comparing date problem [message #122339 is a reply to message #122338]

Mon, 06 June 2005 00:32

charlotte may
Messages: 8
Registered: June 2005

Junior Member

hi,i thinks that u'll find it ridiculous!

o mayb stupid!
i attempt to change the APP_VDATE_TO in the oracle database!
mean that before that is 8-Jun-2005, i try to change to 2-Jun-2005.
it change(mean that i can view the date is 2-Jun-2005 in that table)!
but my friend say that what he get is 8-Jun-2005!

then i try to delete that records and add new records with the APP_VDATE_TO less than today date.
then it work and display records!

sorry for trouble u!
may i know that whether we can straight away change the data in the oracle?
another request, pls help me to solve the latest question that i post!

really hv to give u a big clap and big thanks!

regards;
charlotte

Report message to a moderator

Re: comparing date problem [message #122340 is a reply to message #122339]

Mon, 06 June 2005 00:41

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

It is so often the OTHER things that we did that muck up what we are trying to do.

I think you need to get a couple of books off the web.

Go to http://www.oracle.com/technology/documentation/forms.html. Find 'Guidelines for Building Applications'. Download it. Find 'Form Builder Reference'. Download it.

Alternatively, go to http://www.oracle.com/technology/documentation/index.html. Press 'Search', enter 'to_date', select the 9.2 documentation suite (first hot link), go down to 'SQL Reference' and there are 45 references. Have a look at the 'examples'.

But basically,
"Select * from ESC_APPLICANT where (ISSUE_DATE is january) "
becomes
"Select * from ESC_APPLICANT where to_date(ISSUE_DATE,'MON') ='JAN' "

David

[Updated on: Mon, 06 June 2005 01:02]

Report message to a moderator

Re: comparing date problem [message #122342 is a reply to message #122340]

Mon, 06 June 2005 00:56

charlotte may
Messages: 8
Registered: June 2005

Junior Member

thanks, david!

really thanks... Thumbs Up

Thumbs Up

regards;
charlotte

Report message to a moderator

Previous Topic:	A form to generate sequencial number.
Next Topic:	How to open an Excel file with Password?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 21:25:32 CDT 2024