IF statement inside where clause in SQL

if condition in where clause in sql server 2008
if condition in where clause in sql server 2012
if statement in where clause mysql
if condition in where clause oracle
access sql if statement in where clause
sql update with case statement in where clause
sql case statement in where clause with parameters
sql nested case statement in where clause

I'm developing a calendar app. It stores times in two formats. The table have following columns title, startDate, startTime. If the user provides start time plus start date. The database stores UNIX time stamp(number of seconds since UNIX epok) in the column startTime, while startDate is NULL. If the user only provide a start date, the database stores the date in format nnnn-mm-dd in startDate and NULL in ´startTime`. I have this DB structure, because it's easier to display times around the world, because different timezones have different daylight saving times.

I want select the events that are occurring after a specified date. The client computer provide the server with a UNIX timestamp of the beginning of that day($unix) and a date($date) in the format nnnn-mm-dd to select the correct dates.

The problem is, I don't know how to select those days that are occurring as specified above. This solution is not applicable for me, even though it works:

SELECT *
  FROM events
 WHERE startDate >= '$date'
   OR startTime >= '$unix'

The thing is I have in some rows where unix time stamp is provided in startTime, I also have a date provided in startDate and other reason I which I don't want to explain. And because of that I can't use the solution above.

I need some kind of solution that have an IF statement inside the Where clause like:

SELECT *
  FROM events
 WHERE IF(startTime = NULL, startDate >= '$date', startTime >= '$unix')

I'm just guessing this solution. But is it right?

WHERE (startTime IS NULL AND startDate >= '$date')
   OR (startTime IS NOT NULL AND startTime >= '$unix')

IF STATEMENT IN WHERE CLAUSE – SQLServerCentral, You can do this by using ISNULL Keyword in a Simple Select Statement Hide Copy Code. select * from Doctorslist where DoctorName  IF statement or condition in WHERE clause of SELECT statement in sql server. We cannot use IF statement or condition in WHERE clause of the SELECT statement. It is syntactically incorrect. We can achieve our goal without using if condition in WHERE clause.

All SQL dialects support CASE WHEN:

SELECT *
 FROM events
WHERE CASE WHEN startTime is null
           THEN startDate >= '$date'
           ELSE startTime >= '$unix'

[Solved] How to write If condition inside where clause in SQL , You can use CASE statement instead of IF..ELSE clause to do conditional where clause. Given below is the script. --This script is compatible with  SQL SERVER – How to use ‘if… else’ in ‘where’ clause. IF… ELSE clause is very handy and whenever you need to perform any conditional operation, you can achieve your results using it. But there are some limitations in IF… ELSE, and one of the limitations is that you cannot use it in WHERE clause.

You just need some combined boolean logic in the WHERE clause:

SELECT * 
FROM events
WHERE 
    (startDate IS NULL AND startTime >= '$unix') OR
    (startTime IS NULL AND startDate >= '$date')

SQL SERVER – How to use 'if… else' in 'where' clause, You can try using the CASE statement like this: Refer to this video to learn Case Statements in detail: WHERE OrderNumber LIKE. CASE statements in where clauses are less efficient than boolean cases since if the first check fails, SQL will stop processing the line and continue on. That saves you processing time. Also, always put the more costly statement on the other side of your boolean check.

SELECT * 
FROM events 
WHERE 
  (startDate >= '$date' OR startTime >= '$unix')
AND 
  startDate != NULL

This will not return any row that has null value for startDate

SQL: IF clause within WHERE clause, Then, since 1 = 1 is true, the row is returned. OK, so now you can see that you can use CASE statement within a WHERE clause. Does that mean you should? According to Ms SQL Docs a CASE statement can be used throughout the SELECT statement. CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

I'm assuming that you want to use startDate when startDate is not null, and startTime when startTime is not null... then try this...


SELECT * 
FROM events
WHERE (startTime is not NULL and startDate >= '$date') 
OR (startTime is not NULL and startTime >= '$unix') 

You can use the CASE statement also, but this makes more sense is more readable.

Can I have a CASE Statement in the WHERE Clause?, If the condition is FALSE , then the code block in the ELSE clause is executed. See the It is a good practice to not nest an IF statement inside another statement  Answer: This is a very popular question. This question usually comes up in the context of writing search condition where the user is not sure if there will be condition or not. First of all, as Kalen Dealaney mentioned (Thank you!) on the twitter Case is not a statement it is an expression.

SQL Server IF ELSE Statement By Examples, So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no  Arguments. Boolean_expression Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses. { sql_statement | statement_block } Is any Transact-SQL statement or statement grouping as defined by using a statement block.

https://social.msdn.microsoft.com/Forums/sqlserver, If the condition evaluates to False, then T-SQL statements followed by ELSE languages, you cannot add an ELSE IF statement within an IF… When the condition in the IF clause evaluates to FALSE and you want to execute another statement block, you can use the ELSE clause. The following illustrates the IF ELSE statement: IF Boolean_expression. -- Statement block executes when the Boolean expression is TRUE. -- Statement block executes when the Boolean expression is FALSE.

SQL CASE Statement, IF (condition) THEN   statement END IF; Note that the conditionals, and other statements, are written in all caps, in SQL. With the IF statement, we first check a condition. If it evaluates to true, the statement associated with THEN is executed.

Comments
  • @Woho87 : you can never have enough NULL! ;)
  • @Woho87: there is no two nulls. Was in the first revision but I fixed that typo after 10 seconds.
  • And this will not be optimized to be using indexes.
  • Are you sure we need IS NULL in both cases? If so - why don't move it out the parentheses.
  • @zerkms: different variables...I'm going by my interpretation of the posters requirements, which may or may not be correct: "The "database stores UNIX time stamp(number of seconds since UNIX epok) in the column startTime, while startDate is NULL. If the user only provide a start date, the database stores the date in format nnnn-mm-dd in startDate and NULL in ´startTime`.
  • hehe, and I tried to write my answer according his IF supposition... Which represents different logic :-S
  • != NULL is always evaluated to FALSE
  • AND startDate IS NOT NULL is what you're looking for there @Ibu.