SQL Datedifference in a Where Clause

SQL DateDifference in a where clause

DateDiff is extremely fast... Your problem is you are running it on the database table column value, so the query processor must run the function on every row in the table, even if there was an index on this column. This means it has to load the entire table from disk.

Instead, use the dateAdd function on todays date, and compare the database table column to the result of that single calculation. Now it only runs DateAdd() once, and it can use an index (if one exists), to only load the rows that match the predicate criterion.

Where a.DateValue > DateAdd(day,-3,getdate())

doing this in this way makes your query predicate SARG-able

SQL DATEDIFF with WHERE or BETWEEN operator

The from clause cannot use aliases defined in the select clause. You need to repeat the expression, or use a subquery or cte.

If you are running SQL Server (as your code suggests):

select datediff(day, '2012-01-01', getdate()) as d 
from ...  -- you should have a "from" clause
where datediff(day, '2012-01-01', getdate()) between 50 and 80

In MySQL (as the tag on your question indicates):

select datediff(current_date, '2012-01-01') as d 
from ...
where datediff(current_date, '2012-01-01') between 50 and 80

Datediff in where clause

Parathensis! And in both cases, MainColumn must be non-null. You can simplify your WHERE clause like this:

WHERE MainColumn IS NOT NULL
AND (DATE1 BETWEEN @MonthStart AND @MonthEnd OR DATE2 <= DATEADD(MM, -36,GETDATE()))

Using DATEDIFF in WHERE clause Condition

Make it S'argable (which means an appropriate index might be used if it exists):

WHERE [fld_DateResolved] <= DATEADD(Day, -3, CAST(GETDATE() as Date))

[Updated: added a cast to Date which assumes you are on SQL Server 2008+]

Case Statement in Where Clause for datediff

How about something simpler:

where (datepart(dw, GETDATE()) <> 2 and
       cast(invoice as date) = cast(dateadd(day, -1, getdate()) as date)
      ) or
      (datepart(dw, GETDATE()) = 2 and
       cast(invoice as date) = cast(dateadd(day, -3, getdate()) as date)
      ) 

In general, functions in the where clause prevent the use of indexes. One exception (the only exception?) is casting datetime values to dates. Here is a blog on the subject.

MySQL - Comparing two dates in the WHERE clause

Totally unclear why "year" would be stored as an unsigned bigint. That is way overkill for my understanding of "year".

In any case, why not rephrase the logic to:

WHERE link.parent_category_year = link.child_category_year
      link.parent_category_year = link.child_category_year + 1 

In general, you should not have a problem adding 1 to an unsigned value.

DATEDIFF based on ID in WHERE clause

How about just using aggregation? Assuming that the 33s are always before the 49 (which seems to be the case based on the sample data):

select id
from @cte
group by id
having datediff(second,
                max(case when eid = 33 then datetime end),
                max(case when eid = 49 then datetime end)
               ) < 35;

Date difference = 0 in where clause Oracle?

What is INSERTION_DATE's datatype?

If it is DATE, then comparing it to another date (note: this is date literal; value you used is a string!)

select * from table where insertion_date = date '2018-11-20'

might work, unless INSERTION_DATE contains time component (hours and minutes). Then, the simplest option is to truncate its value (so that you get date itself, at midnight):

select * from table where trunc(insertion_date) = date '2018-11-20'

but it'll ruin index you have on that column (unless it is a function-based one). For small tables, it won't make any difference. For large amount of data, it would so convert it to

select * from table where insertion_date >= date '2018-11-20'
                      and insertion_date <  date '2018-11-21'

If, on the other hand, INSERTION_DATE is a string (VARCHAR2 or CHAR) datatype (which is a really bad idea; consider switching to DATE datatype), then you have to know its format, convert it to DATE first and then compare to another date. For example, if it was a string that contains date values in format dd.mm.yyyy, then

select * from table where to_date(insertion_date, 'dd.mm.yyyy') = date '2018-11-20'

This will certainly fail if any string in that column doesn't match such a format or contains invalid values (such as "date" 53.67.Bx48).

---

Related Topics