difference between two timings and the result can be used in if condition

Question

1.00/5 (1 vote)

See more:

Can any one help me to find out the difference between two timings and the result can be used in
if condition
i.e.,diff= 08:00:00

SQL

if(diff= 08:00:00)
Set status = 'p'
else
set status ='A'
    &
inpunch= NULL,Outpunch = NULL

here if i want to update the status it is not executing can any one help me why it was happened.

I want to update the status by considering the in & out punches
eg: inpunch = 08:00:00
outpuncnch = 12:00:00
status as 'HL'

remaining in a similar manner what we are caluculating for attendance purpose

Posted 22-Dec-13 18:51pm

Karthik Achari

Updated 23-Dec-13 0:57am

Nelek

v3

Add a Solution

Comments

Karthik Achari 24-Dec-13 3:20am

Thanks

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)

Maciej Los · Accepted Answer · 2013-12-22T20:40:00

Solution 1

Try something like that:

SQL

IF DATEDIFF(hh, @starttime, @endtime) = 8
BEGIN
    --true part
END
ELSE
BEGIN
    --false part
END

For further information, please see:
DATEDIFF[^]
IF...ELSE[^]

Posted 22-Dec-13 20:40pm

Maciej Los

Comments

Karthik Achari 24-Dec-13 3:21am

Thanks

Maciej Los 24-Dec-13 3:23am

You're very welcome ;)

Christian Graus 24-Dec-13 3:41am

Be careful, I doubt the difference is rarely exactly 8. I would suggest using FLOOR at least, so that anything from 8.0 to 8.9999 matches, but I'd think that something like:

IF floor(DATEDIFF(hh, @starttime, @endtime) + .5) = 8

which would give you 7.5 to 8.5 as an acceptable range. It depends on your business rules, but requiring exactly 8.0 is only good if your values are all in exact chucks ( for example all your date times have no seconds and are in fifteen minute increments )

The other thing, if you're measuring if someone worked an 8 hour day, for example, is to use >=

Maciej Los 24-Dec-13 3:50am

Thank you for your comment, Christian.

Christian Graus 24-Dec-13 3:52am

Your answer may be spot on. As always, the issue is missing information in the questions.... It's certainly right, in any case, it just might be right for his test data, and not for the real world :-) I want to write an article encouraging people with SQL questions to give us scripts that create their tables and some test data, and show how to easily generate that, using SQL.

Maciej Los 24-Dec-13 4:08am

I can't wait. I really appreciate your knowledge about SQL.
I need to polish my English to write my articles...
;)

Christian Graus 24-Dec-13 4:09am

*grin* thanks. To be honest, I am writing them in part because I am filling gaps for myself by researching to make sure I cover everything accurately. I hope they help others, too.