10

i have one question about get multi rows before the date getting infection:

patientID   Appt_reason dateofProcedure
1                        1/2/2016
1                        1/3/2016
1             infectoin  1/4/2016
2                        3/1/2016
2                        3/3/2016
3                        3/4/2016
3             infectoin  3/5/2016
3                        3/6/2016
3                        3/7/2016
5                        2/2/2016
5             infectoin  2/3/2016
5                        2/4/2016

I need to get rows like this

patientID   Appt_reason dateofProcedure
1                       1/2/2016
1                       1/3/2016
1          infectoin    1/4/2016
3                       3/4/2016
3          infectoin    3/5/2016
5                       2/2/2016
5          infectoin    2/3/2016

anybody know the answer? thanks

Dai
  • 141,631
  • 28
  • 261
  • 374
joan
  • 101
  • 1
  • 1
  • 4

1 Answers1

18
SELECT
    patientID,
    Appt_reason,
    dateOfProcedure
FROM
    tableName
WHERE
    dateOfProcedure < '2016-04-03'

Note I'm using ISO-8601 format which is unambiguous and supported by all modern database systems.

Note that it's using the < (less-than) operator instead of <= (less-than-or-equal to) because in SQL a date literal value can represent datetime values, so '2016-02-01' >= '2016-02-01 01:00' is actually false, because '2016-02-01' has a hidden time component equal to midnight, and 1am is after midnight.

Dai
  • 141,631
  • 28
  • 261
  • 374
  • I think dateOfProcedure < multiple time, not certain time. – joan Nov 04 '16 at 03:39
  • In SQL (the query language) a `DATE` can **not** be a "datetime". There are two distinct data types in SQL: `DATE` and `TIMESTAMP` the latter including a time. The only DBMS that I am aware of that includes a time even with a plain `DATE` is Oracle. –  Nov 04 '16 at 06:49
  • @a_horse_with_no_name that's why I put emphasis on the "can" word, because if the column is `datetime` (instead of `date`) then a `'yyyy-MM-dd'` literal or parameterised value would be compared as a `datetime` instead of a `date`. The OP didn't say what the type of the `dateOfProcedure` column was. – Dai Nov 04 '16 at 06:53