IF Condition in an SQL query

Question

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

Answer 1

select *
from table
where DATEDIFF(day, GETDATE(), maildate) = case when
    DATEPART(hour, GETDATE()) >= 16 then 1 else 0
end

Answer 2

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)
     ;   

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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 ) )  ;