Geek Answers Handbook

IF condition in SQL query

I am new to SQL Server. Please help me write the following logic in the query.

If getnow() > today 4 PM Then SELECT * FROM table WHERE MailDate is Tomorrow Else SELECT * FROM table WHERE MailDate is Today
+8
sql-server if-statement
source share
8 answers
 select * from table where DATEDIFF(day, GETDATE(), maildate) = case when DATEPART(hour, GETDATE()) >= 16 then 1 else 0 end
+6
source share
 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
+3
source share

I don’t know the exact MS syntactic dialect, but I will try to prove that you do not need the IF or CASE construct. I took @mellamokb 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) ;
+1
source share

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 )

equivalently

 ( NOT ( DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16 ) OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) )

and itself is equivalent

 ( ( DATEPART(HOUR, CURRENT_TIMESTAMP) < 16 ) OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 1 ) )

Rewriting the original ELSE as an independent IF..THEN expression:

 IF ( DATEPART(HOUR, CURRENT_TIMESTAMP) < 16 ) THEN ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 )

equivalent (this time skipping the intermediate step)

 ( ( DATEPART(HOUR, CURRENT_TIMESTAMP) >= 16 ) OR ( DATEDIFF(DAY, CURRENT_TIMESTAMP, MailDate) = 0 ) )

Then two expressions can be written in conjunctive normal form ("a series of AND s)

 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 ) ) ;
+1
source share

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

+1
source share

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

0
source share
 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 than one command / select inside, if you do BEGIN .... END.

0
source share
 IF DATEPART(HOUR, GETDATE()) > 16 BEGIN -- SELECT statement END ELSE BEGIN -- SELECT statement END

It should not be in a stored procedure.

0
source share

More articles:


All Articles