cancel
Showing results for 
Search instead for 
Did you mean: 

How to compare dates in a select query in open sql

Former Member
0 Kudos

Hi

How can I retrieve data pertaining to a specified date using a select query.

I have inserted date using the following code:

String dateString = "2009-03-11";

java.sql.Date date = java.sql.Date.valueOf(dateString);

PreparedStatement stmt =con.prepareStatement("insert into DATEACCESS"

+ "(DATETEST)"

+ "values (?)");

stmt.setDate(1,date);

stmt.executeUpdate();

How can I pass this in a select query,the below mentioned query did not fetch me any results.

Select * from DATEACCESS where DATETEST='2009-03-11'

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi ,

If u r usung a webservice .

When u r inserting the Date u r using sql Date . In the where clause don't give the date hard coded .

You have declared a Date variable try to pass that in the select query .

Edited by: Anuradha Rao on Mar 19, 2009 3:50 PM

Edited by: Anuradha Rao on Mar 19, 2009 3:54 PM

Former Member
0 Kudos

Hi,

Thanks for the response.

I tried using date object directly in the query but it did not work.

long utcMillisRepresentingNow = System.currentTimeMillis();

long normalizedMillis =com.sap.sql.DateTimeNormalizer.normalizeSqlDateMillies(utcMillisRepresentingNow);

java.sql.Date today=new java.sql.Date(normalizedMillis);

String query2="select * from DATEACCESS where DATETEST<=today";

Former Member
0 Kudos

Hi

Wht error or exception is it giving u .

Former Member
0 Kudos

Hi,

Its giving me the following error -

The SQL statement "SELECT * FROM "DATEACCESS" WHERE "DATETEST" <= "today"" contains the semantics error[s]: - 1:47 - the column >>today<< is undefined in the current scope .

My query was:-

String query2="select * from DATEACCESS where DATETEST<=today";

Former Member
0 Kudos

Hi

Has ur date problem resolved ?

If not resolved u can do the following

As u already have sql date today declared . You want to get the data depending on that date

As ur query is

if( today != null)

String query2 = select * from DATEACCESS where DATETEST<= ?

PreparedStatement ps = conn.prepareStatement(query2);

ps.setDate(1,today)

ResultSet rs = ps.executeQuery();

While(rs.next)

{

////// Here u will get data from query u needed ///

}

If this also does not work just print the date and check in which format is it returning u .

Regards

Anuradha Rao

Edited by: Anuradha Rao on Mar 20, 2009 10:16 AM

Former Member
0 Kudos

Hi

Thanks for the response.My problem is not yet solved,I tried executing the query below:-

String query2="select * from TMP_DATEACCESSF where DATETEST=?";

pstmt=con.prepareStatement(query2);

pstmt.setDate(1,thur);

Values in my table are stored in this format:-

2009-03-11

2009-03-18

Can you let me know if it possible to achieve this using NativeSQLAccess

Former Member
0 Kudos

Hi ,

Are u trying to use this code for Web Service ?

Former Member
0 Kudos

Hi

I am not going to use this in a web service

Former Member
0 Kudos

Hi

If u r using in in the Webservice then u wil be having some input parameters . As ur query has date in it . U want all the Data from the Table for that date am i right . So ur date is an Input Parameter from the web service or ur passing the Date to fetch some internal Data .

Could u paste the web service code i mean the method in which u r using this date query . If its possible for u .

Thanks & Regards

Anuradha Rao

Former Member
0 Kudos

Hi,

I am just trying to retrieve the list of people from the table whose name was entered into the table in the past two weeks

Former Member
0 Kudos

Hi

I was able to insert and retrieve the date using NativeSQLAccess which allows me to enter query in oracle,but I am not able to insert multiple values. this is how I wrote my insertquery:-

String query="insert into DateAccess (name,date) values(?,sysdate);

ps.setString(1,"apple");

But no insertion seems to happen