10

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

If getnow() > today 4 PM
Then
    SELECT *
    FROM table
    WHERE MailDate is Tomorrow
Else
    SELECT *
    FROM table
    WHERE MailDate is Today
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1345260
  • 2,151
  • 12
  • 33
  • 42

8 Answers8

8
select *
from table
where DATEDIFF(day, GETDATE(), maildate) = case when
    DATEPART(hour, GETDATE()) >= 16 then 1 else 0
end
RobIII
  • 8,488
  • 2
  • 43
  • 93
  • 2
    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 '12 at 00: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 '12 at 00:10
3
IF datepart(hh, getdate()) >= 16
    BEGIN
        SELECT *
        FROM table
        WHERE DateDiff(day, getdate(), MailDate) = 1
    END
ELSE
    BEGIN
        SELECT *
        FROM table
        WHERE DateDiff(day, getdate(), MailDate) = 0
    END
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 1
    Why the ugly casting back-and-forth (with the "magic [style-constant](http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx)" (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 '12 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 '12 at 23:16
  • 1
    I've deleted my answer since they are now essentially the same. But I'll post the demo here: http://www.sqlfiddle.com/#!3/96767/1. Note that you'll want `>= 16` otherwise you're starting at 5 PM. – mellamokb Apr 19 '12 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 '12 at 00:02
2

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 ) ) 
       AND 
       ( ( (DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16
          OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 ) )  ;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

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)
     ;
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • 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 '12 at 00:07
1

select a.name, if(a.is_active='t','Active','Inactive') from mytable a

user1884500
  • 269
  • 3
  • 3
0

You need a stored procedure to do this in SQL. Have a look at the docs here http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

Caumons
  • 9,341
  • 14
  • 68
  • 82
0
IF ( DATEDIFF(h, GETDATE(), DATEADD(h,4,GETDATE()))>0 )

SELECT *
FROM table
WHERE MailDate is Tomorrow

ELSE

SELECT *
FROM table
WHERE MailDate is Today

This is MS SQL. If you want to do more then just one command/select inside the if you do BEGIN .... END.

YvesR
  • 5,922
  • 6
  • 43
  • 70
  • Since when does MSSQL support `WHERE MailDate is Tomorrow` or `WHERE MailDate is Today`? Also, the [`is`](http://msdn.microsoft.com/en-us/library/aa933227(v=sql.80).aspx) operator is intended for comparing to `null`, nothing else. – RobIII Apr 19 '12 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 '12 at 23:48
0
IF DATEPART(HOUR, GETDATE()) > 16
BEGIN
    -- SELECT statement
END
ELSE 
BEGIN
    -- SELECT statement
END

It does not have to be in a stored procedure.

AttilaD
  • 1
  • 3
    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 '12 at 23:10