0

Hi I am trying to query records in a sql database based on a date and time column. I want to select all records that are past a specific date. Here is what I tried.

SELECT * FROM `table` WHERE status = 1 AND odate >= ? AND otime > ?;

Which this will not work because even if the date value is greater the time has to be greater as well. I am not sure how this can be done? I am using MS SQL.

Here is test case...

DB Record || odate: 12/20/2013 otime: 4:55:00 PM.

Passed Params || date: 12/15/2013 time: 6:13:00 PM

Even though the odate > date the otime is not > than time.

So my query, 

AND odate >= ? AND otime > ?;

fails.

user2868614
  • 85
  • 1
  • 7
  • Why not just save your data in a `DATETIME` column? – Mureinik Dec 20 '13 at 19:11
  • 1
    Is this MySQL or Microsoft? In either case show sample data explaining the datatypes used on these columns. – AgRizzo Dec 20 '13 at 19:12
  • @Mureinik it is not my sql db to manipulate. -AgRizzo both data types are DateTime apparently. but the otime does not store the date section, only the time. – user2868614 Dec 20 '13 at 20:02
  • OP, to clarify AgRizzo's question, MySQL and SQL Server are two different RDBMS products owned by two different companies (Oracle and Microsoft, respectively.) Virtually all RDBMS vendors have proprietary language features, so it is important to know which RDBMS you are working with. – Mike Dec 21 '13 at 02:36

2 Answers2

1

If odate and otime are both of data type datetime, just add the date and time together.

Where odate + otime > ?;

If they are not of data type datetime, then just convert them to datetime first.

See this stackoverflow answer.

Community
  • 1
  • 1
Mike
  • 3,641
  • 3
  • 29
  • 39
0

Here is how you get today's date:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

Here is how you get the current time:

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

Apply these to your fields in your table. Also, I can't really tell but it looks like your DateTime field is called "date"? That's a reserved word and will cause you problems when you use it. If that's true, and you can't change it, you need to encase it in brackets (like this: [date]) or it will try to resolve the date rather than pick up the value of your field.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • It is called `odate` I accidentally put date. I'm not very interested in obtaining the date/time value. I want to select * rows by comparing 2 DateTime columns `odate` `otime` against specific date and time values. This way I can get all rows past a certain date for example. – user2868614 Dec 20 '13 at 20:05
  • SELECT * FROM `table` WHERE status = 1 AND odate >= CONVERT(VARCHAR(10), ?, 101) AS [MM/DD/YYYY] AND otime > ?; – Johnny Bones Dec 20 '13 at 20:15
  • hmm... not sure we are on the same page. Comparing the values is not the issue I am having. The problem lies within the otime. The (odate >= #?#) will return all rows that have a greater date, thats working 100%. Now if I want to be more specific and compare the date AND time it will fail. – user2868614 Dec 20 '13 at 20:27
  • I'm not understanding where time is coming from. You're storing time in one field and then plugging in time as a variable? Or are you comparing it to current time? – Johnny Bones Dec 20 '13 at 20:29
  • You should only compare time if the dates are identical. Maybe you need to spin that into a CASE statement? – Johnny Bones Dec 20 '13 at 20:31
  • ^ yes exactly. I have never made a case statement before. Could you provide the code for this? Thank you very much! – user2868614 Dec 20 '13 at 20:55