getting the data based on dates

[Former Member]

Hi All,

In my application i am not able to retrieve the data from database between two dates.

while entering the dates i am entring using :

+ "',to_date('+ fromdate+ "','DD-Mon-YYYY HH24:MI:SS'),"

as in database i have kept my fromdate type as "varchar2"

So while selecting data i have written the query as :

String sqlQuery =

"select SID,SERVER_NAME,DOWNTIMETYPE,FROMDATE,TODATE,FROMTIME,TOTIME,TIMETAKEN,DOWN_REASON from SS_UPDOWNTIME where (FROMDATE >='"

+ frmdate

+ "' OR (FROMDATE between '"

+ frmdate

+ "' and '"

+ todate

+ "')) ";

but i am not able to get the data between the two selected dates

Regards,

Anupama

[former_member205363 · Accepted solution]

Hi Anupama,

'between' wont work because you have taken dates as varchar2.

Just try like this.

Take "Date" type in database and in java take java.sql.Date

java.sql.Date sqlDefaultDate = new java.sql.Date(System.currentTimeMillis());

System.out.println("sqlDate: "+sqlDefaultDate);

// this will be in the format 2008-11-13 , So we can pass sqlDefaultDate variable to backend

Regards,

Lakshmi Prasad.

[Former Member · Accepted solution]

hey thanx for your help ..actually i tried this thing many times but problem here exists is that following syntax is not working:::

SQL> select

SID,SERVER_NAME,DOWNTIMETYPE,FROMDATE,TODATE,FROMTIME,TOTIME,TIMETAKEN,

DOWN_REASON

from

SS_UPDOWNTIME

where

FROMDATE >='29-Oct-08' OR (FROMDATE between '29-Oct-08' and '12-Nov-08');

when i am doing less than it is working fine..Now what the problem will be here ?

Regards,

Anupama

[former_member205363 · Accepted solution]

Hi,

Right now I dont have Toad to test this query, try this way.

SELECT * FROM Schedules WHERE Start >= DateValue('04/10/2001') AND End <= DateValue('11/10/2001')

Regards,

Lakshmi Prasad.

[Former Member · Accepted solution]

Hey Lakshmi,

But I am getting the error as :::

ERROR at line 1:

ORA-00904: "DATEVALUE": invalid identifier

What to do next ?

Regards,

Anu

[Former Member · Accepted solution]

Hi,

Have a work around to use To_Char function. You should use To_Char function that converts date into char and then you can compare them like this

SQL> select

SID,SERVER_NAME,DOWNTIMETYPE,FROMDATE,TODATE,FROMTIME,TOTIME,TIMETAKEN,

DOWN_REASON

from

SS_UPDOWNTIME

where

TO_CHAR(FROMDATE,'DD-MON-YY) >='29-Oct-08'

Regards

Raghu

[Former Member · Accepted solution]

thnx all of you for your support

finally i did it by

::: (TO_date(FROMDATE,'DD-MON-YY')between ' frmdate' and ' todate')

where fromdate is column having varchar2 as datatype.

and

frmdate & todate :::user has selected from date picker

Regards,

Anupama