Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm a newbie to SQL Server. Please help me to write the following Logic in a query.

If getnow() > today 4 PM
    SELECT *
    FROM table
    WHERE MailDate is Tomorrow
    SELECT *
    FROM table
    WHERE MailDate is Today
from table
where DATEDIFF(day, GETDATE(), maildate) = case when
    DATEPART(hour, GETDATE()) >= 16 then 1 else 0
                Hey RobIII, buddy. Just wanted to say, I did upvote you, but the downvotes may be a result of the conversations in this post.  Don't take it personally, we can all always learn something :) Just remember to think about what you're saying as you post it and consider how it might come across to others.  I and others here strive hard to make SO a friendly community that welcomes all skill levels and gives everyone a chance.  It looks like yours has been marked as the answer as well, so congrats, and have a great day!
– mellamokb
                Apr 20, 2012 at 0:08
                Roblll, I upvoted you and if it makes you feel any better, I would like to say that I implemented your code as it looked much efficient. Of course it took a while for me to understand it. :) Thanks a lot
– user1345260
                Apr 20, 2012 at 0:10
                Why the ugly casting back-and-forth (with the "magic style-constant" (e.g. 101) codes nobody can ever remember, varchars, a timevalue in a string including a space which, if forgotten, will break everything etc.) if there are datetime functions?
– RobIII
                Apr 19, 2012 at 23:14
                @RobIII you are right it is fixed. I was just providing another way to do it but I didn't say it was the best way.
– Taryn
                Apr 19, 2012 at 23:16
                I've deleted my answer since they are now essentially the same.  But I'll post the demo here: sqlfiddle.com/#!3/96767/1.  Note that you'll want >= 16 otherwise you're starting at 5 PM.
– mellamokb
                Apr 19, 2012 at 23:56
                @mellamokb thanks for pointing out the >= obviously I missed that.  I didn't realize they were the same after my initial edit.  I felt brow-beat into removing the converts. I know they are not the best to convert back and forth, I was just showing a different albeit ugly way. :)
– Taryn
                Apr 20, 2012 at 0:02
The idea here is to use the implication rewrite rule:
IF ( x ) THEN ( y )   is equivalent to  ( NOT ( x ) OR y )
In your case
IF ( DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16 ) 
   THEN ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) 
is equivalent to
( NOT ( DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16 ) 
   OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) ) 
and is itself equivalent to
( ( DATEPART(HOUR, CURRENT_TIMESTAMP) < 16 ) 
   OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) )
Re-writing the original ELSE clause as an IF..THEN statement in its own right:
IF ( DATEPART(HOUR, CURRENT_TIMESTAMP) < 16 ) 
   THEN ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 ) 
is equivalent to (this time omiting the intermediate step)
( ( DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16 )
   OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 ) )
The two expression can then be writting in conjunctive normal form ("a series of ANDs)
SELECT *
  FROM the_table
 WHERE ( ( DATEPART(HOUR, CURRENT_TIMESTAMP) < 16 ) 
          OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) ) 
       ( ( (DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16
          OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 ) )  ;
I don't know the exact MS-syntax-dialect, but I'll try to prove that you don't need an IF or a CASE construct. I took @mellamokb 's reply as an example.
SELECT *
  FROM the_table
 WHERE ( DATEPART(hour, GETDATE()) >= 16
           AND DATEDIFF(day, GETDATE(), MailDate) = 1)
    OR (DATEPART(hour, GETDATE()) < 16
           AND DATEDIFF(day, GETDATE(), MailDate) = 0)
                Except that your "quest to avoid a case when/else" results in a where clause that needs several evaluations to be done for each record whereas my case when... can be evaluated once and then only needs one evaluation per record.
– RobIII
                Apr 22, 2012 at 0:07
                Since when does MSSQL support WHERE MailDate is Tomorrow or WHERE MailDate is Today? Also, the is operator is intended for comparing to null, nothing else.
– RobIII
                Apr 19, 2012 at 23:09
                @RobIII I just copied the two statement, didn't see that. Anyway he can simple you the same I wrote in the if clause, it is just the same
– YvesR
                Apr 19, 2012 at 23:48
                Commenting on everyone else's answer and promoting your own is not very helpful or community-oriented.  Please refrain from doing it in the future.
– mellamokb
                Apr 19, 2012 at 23:10
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.