sql query to get data by date and time

Question

I want to get the rows where date and time are greater then given date. Here is my table:

EvDate      EvTime      ClBdg   Name    Event
18.01.2015  10:55:01    001     Jane    enter
18.01.2015  19:31:21    003     Brad    exit
19.01.2015  13:31:21    002     Lucy    exit

Given date is 18.01.2015, time is 17:00:00. Desired values are:

EvDate      EvTime      ClBdg   Name    Event
18.01.2015  19:31:21    003     Brad    exit
19.01.2015  13:31:21    002     Lucy    exit

Can anyone helps me?

Edit: To be more clear I edited my post. Here is the java code to execute the query:

private static final SimpleDateFormat sdfTime=new SimpleDateFormat("HH:mm:ss");  
private static final SimpleDateFormat sdfDate=new SimpleDateFormat("dd.MM.yyyy");  
private long givenTimeStamp = 1421600400L;
public static Date givenDate = new Date(givenTimeStamp);

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");  
String DATABASE = "jdbc:ucanaccess://"+CONF.getString("db.location")+";jackcessOpener=uz.lexus.access.crypto.MyCryptoProvider";  
connection= DriverManager.getConnection(DATABASE, null, null);  

List<Event> events=new ArrayList<Event>();  
String selectEvents = "select EvDate,EvTime,ClBdg,Event from Evntlog_tbl where  Event in ('enter','exit') and not ClBdg=0 and EvDate >= ? and EvTime > ?  order by EvDate desc, EvTime desc";
PreparedStatement prstm= connection.prepareStatement(selectEvent);  
java.sql.Date date=new java.sql.Date(sdfDate.parse(sdfDate.format()).getTime(givenDate));  
java.sql.Time time=new Time(sdfTime.parse(sdfTime.format(givenDate)).getTime());  

prstm.setDate(1,date);  
prstm.setTime(2,time); 

ResultSet rs = prstm.executeQuery();

Now the question is: Is the query in the selectEvent correct?

What flavor of SQL (or what **concrete** database) are you using? Those are all slightly different in how they deal with these things! Please add a relevant tag (like `oracle`, `postgresql`, `mysql`, `db2`, `sql-server` etc. to your question!) — marc_s, Jan 19 '15 at 10:27
Did you tried anything ? If not then read about `WHERE` clause in sql server here is the link http://msdn.microsoft.com/en-us/library/ms188047.aspx — Mahesh, Jan 19 '15 at 10:32
I have tried "SELECT * FROM EvntLog_tbl WHERE EvDate >= '18.01.2015' AND EvTime > '17:00:00';" . But it gives wrong rows — LeXuS, Jan 19 '15 at 10:37
The problem is the logic, not the types. As it is, the query asks for future events that occur only after `17:00`, where it should ask either for all events after the parameters date, or those that occur after `17:00` on the same date — Panagiotis Kanavos, Jan 19 '15 at 10:53
@PanagiotisKanavos I'v edited the post, can you suggest anything now? — LeXuS, Jan 19 '15 at 11:28

varsha · Accepted Answer · 2015-01-20T05:23:30.417

4

try this , As you have two different columns for Evdate and EvTime

SELECT * FROM table_name WHERE EvDate >'18.01.2015'  OR (EvDate = '18.01.2015' AND EvTime > '17:00:00'

Update-

attaching a fiddle , will be helpful .

edited Jan 20 '15 at 05:23

answered Jan 19 '15 at 10:34

varsha

1,620
1
16
29

2

@LeXuS *this* is probably the optimal query (assuming a cast to datetime is added). It ensures any underlying indexes will be used by the query optimizer. Applying functions to columns prevents the optimizer from using indexes, although it's usually smart enough to handle some date operations – Panagiotis Kanavos Jan 19 '15 at 10:46
2

@LeXuS if you have created index on any one of these both field or on field then i would like to suggest you to use this query. – Shell Jan 19 '15 at 10:54
@PanagiotisKanavos how can I write it in java – LeXuS Jan 19 '15 at 11:33
@Nimesh Thanks for your sugession, but to write the query in this condition would be more complicated – LeXuS Jan 19 '15 at 11:36
@LeXuS then u should create a view with the combined field `(EvDate + EvTime) As EvDateTime` and try to retrieve the records from that view. you can also create index on it. – Shell Jan 19 '15 at 11:53
@LeXuS more complicated than what? The point is to get both the correct results and good performance. You can create an indexed view with a single datetime column as Nimesh suggests, or add a calculated column with an index on it but you can argue that this is *more* complex. – Panagiotis Kanavos Jan 19 '15 at 11:55
@PanagiotisKanavos I said so because I'm new in sql queries, so in programming – LeXuS Jan 19 '15 at 12:03
@varsha I've tried like this: `selectEvents = "select EvDate,EvTime,ClBdg,Event from Evntlog_tbl where Event in ('enter','exit') and not ClBdg=0 and (EvDate = ? OR (EvDate > ? and EvTime > ?)) order by EvDate desc, EvTime desc";` – LeXuS Jan 19 '15 at 12:08
@LeXuS I could only help u in query as java is not my domain. happy that it worked for you . happy coding . :) – varsha Jan 20 '15 at 05:01
1

@LeXuS take a look at this [fiddle](http://sqlfiddle.com/#!3/8f326/1/0). Varsha you can add this example in your answer as a different approach. – Shell Jan 20 '15 at 05:19

t-clausen.dk · Answer 2 · 2015-01-19T11:53:20.077

3

SELECT *  
FROM table1 
WHERE EvDate + EvTime > '2015-01-18T17:00:00'

Instead you could consider creating a PERSISTED computed column that adds date and time. Persisted computed columns will allow index

ALTER TABLE table1 ADD cEVdatetime AS EvDate + EvTime PERSISTED

edited Jan 19 '15 at 11:53

answered Jan 19 '15 at 10:42

t-clausen.dk

43,517
12
59
92

I wonder, is the query optimizer smart enough to use any indexes on `EvDate` and `EvTime` or is the addition going to force an index scan? – Panagiotis Kanavos Jan 19 '15 at 10:48
If any index is created on any field then it will be removed and the performance may get down. – Shell Jan 19 '15 at 10:52
@Nimesh not so , which is why I ask. SQL Server's optimizer is usually able to convert `DATEADD` or addition operations to range seeks. – Panagiotis Kanavos Jan 19 '15 at 10:55

score 0 · Answer 3 · answered Jan 19 '15 at 10:34

0

This might hepls you

declare @t table(a date,b time)
insert into  @t values (getdate(),'7:00:00 AM')

select * from @t
where a>'2015/01/19' or (a=getdate() and a>'6:00:00 AM')

answered Jan 19 '15 at 10:34

koushik veldanda

1,079
10
23

score 0 · Answer 4 · answered Jan 19 '15 at 10:49

0

i am assume that EvDate Datatype is Date and EvTime Datatype is Time . Try This ....

select * from yourtablename where CONVERT(VARCHAR(20),EvDate ,112)>=20150118 AND EvTime >='17:00:00'

answered Jan 19 '15 at 10:49

pradip vaghani

182
5

The typical advice is to convert strings to proper dates and times, not the other way around. Moreover, this solves nothing. The problem isn't the (non-existent) format of the `datetime` type, it's checking `EvTime` even for future dates – Panagiotis Kanavos Jan 19 '15 at 10:51

sql query to get data by date and time

4 Answers4