I have a Database which is dealing with a book library system. I have a table which has a has a field dealing with the ReturnDate of a book. I was wondering if it is possible to do a SELECT statement that would return the the fines due on an overdrawn book at the database level instead of me having to do it in a Java environment.

In words what I want done:
IF (ReturnDate > CURDATE())
(ReturnDate - CURDATE()) * Fine

Thanks in advance
Paidi

Recommended Answers

All 2 Replies

I have a Database which is dealing with a book library system. I have a table which has a has a field dealing with the ReturnDate of a book. I was wondering if it is possible to do a SELECT statement that would return the the fines due on an overdrawn book at the database level instead of me having to do it in a Java environment.

In words what I want done:
IF (ReturnDate > CURDATE())
(ReturnDate - CURDATE()) * Fine

Thanks in advance
Paidi

select 	id,
	( cast(current_timestamp as date) - cast(return_date as date) ) * 3 as fine
from books
where
  return_date > current_timestamp

This is how to do the select in postgresql however the syntax is going to be slightly different in MySQL (i think you should use now() instead of current_timestamp but I can't remember)

Thank you very much for your help. Its returning figures, hence no error messages. It makes life so much easier to be able to do this calc back at the database level and saves me so much programming time.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.