Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Writing a query that uses the current date

 
vernon mweetwa
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

i have a database of patients that has a field "Last Pharmacy Visit Date", that sores the date the patient last got medicine from the pharmacy. I would like to calculate how many days a patient is late when they don't come to get their medicine on time. The number of days late is calculated by the difference between the current date and the Last pharmacy visit date.

How do i write a query that will take the current system date as a parameter in the query and be able to give a count of how many patients in the database are 30 days or less late.

Your response will be highly appreciated.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just use a Date in your query. Have a look at the Calendar class, and its GregorianCalendar implementation. You should be able to see how to get the current time from that.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although you can do it in your SQL query, but its better to do it in your code, as Paul suggested. Because this requirement comes in business logic, therefore it should not be dealt at database level.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vernon,
Most DBMS's have built-in functions that return the current system date, as well as functions that do date arithmetic. However they are usually proprietary functions -- so if database independence is important to you, this doesn't help you much. Perhaps you would care to inform us as to whether database independence is important to you (or not). If it is not, then if you told us what DBMS you are using, someone may be able to help you further.

Good Luck,
Avi.
 
vernon mweetwa
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

below is a kind of query i need to run to get people that are late for picking drugs in the last 30 days. The first question mark is where the current date should be and the second question mark should be the current date minus 30 days. How do i fit these 2 dates in the query because what i have always learnt on queries is using values that are in the database but now i have to use values that are calculated in the code and are not any field in the database. How do i go about it?.

SELECT count( patient_id ) from master_patient " +
"WHERE first_drugs_dispensed >= ? " +
"AND first_drugs_dispensed < ? " );
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic