calculate Library fine in by comparing two dates SQL server

Question

5.00/5 (1 vote)

See more:

i am devloping library management system in asp.net C#. i want to calculate fine by comparing isuue date and return date. Here

Fine is calculated as:

Find the no of days between issue date and return date.

If no of days are greater than 10 then calculate fine using the formula given below

Fine=No. of Days * 10 Rs. (i.e 10 Rs fine per day)

Otherwise there will be no fine.

So i am inserting the record of isuue date in issue table (data type- Date in sql server)

so how i calculate by comparing the return date. please provide sagment of code for it.

this code can given error

SQL

DECLARE @IssueDate date, @Days int
SET @IssueDate = (select IssueDate from tbl_LibraryBookIssue)
SET @Days = DATEDIFF(DAY,(select ReturnDate from tbl_LibraryBookIssue), GETDATE())
SELECT (CASE WHEN @Days > 11 THEN @Days * 10 ELSE 0 END) Fine

Posted 2-Jan-15 22:54pm

krishna97

Updated 2-Jan-15 23:17pm

v4

Add a Solution

Comments

Wendelius 3-Jan-15 5:12am

The question isn't very clear. Please add more explanations what you're trying to achieve.

krishna97 3-Jan-15 5:16am

how can calculate library Book return fine

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-01-02T23:28:00

No, that won't work.
Try the simpler version:

SQL

SELECT DATEDIFF(d, IssueDate, ReturnDate) * 10 as Fine from tbl_LibraryBookIssue
WHERE DATEDIFF(d, IssueDate, ReturnDate) > 11

not given out put

SQL

declare @IssueDate datetime,@ReturnDate datetime,@Curr datetime
 
set @IssueDate='2014-12-25 00:00:00.000'
Set @ReturnDate='2015-01-01 00:00:00.000'
Set @Curr=GETDATE()
 
--2 day Extra can fine given
SELECT DATEDIFF(d, @ReturnDate, @Curr) * 10 as Fine from tbl_LibraryBookIssue
WHERE DATEDIFF(d, @IssueDate,@ReturnDate) > 7
--select * from tbl_LibryMemberCategory

Um...

25, 26, 27, 28, 29, 30, 31, 1
   1   2   3   4   5   6   7

So the date diff is equal to 7, not greater than...