Linked Questions

3 votes
5 answers
2k views

Optimizing SQL Server Query When Joining Tables on DATETIME column

I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ...
2 votes
2 answers
2k views

WHERE Clause Compare VarChar(n) Column = VarChar(MAX) Variable Results in Inequality Comparison and Poor Query Performance

Earlier today I was working on a Reporting Stored Procedure which had poor performance. I was able to fix the issue by changing a static variable which held a string of ten characters or less from ...
0 votes
2 answers
4k views

Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison? [duplicate]

This is a very common need in the WHERE clause of our SQL statements: CAST(Something.CreationDate AS DATE) BETWEEN CAST(@firstDate AS DATE) AND CAST(@lastDate AS DATE) All of the fields are of ...
21 votes
4 answers
52k views

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My ...
2 votes
2 answers
572 views

Is there a cutoff when using datetime and date datatypes in the where clause?

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997. I then have a where clause that reads as follows: WHERE achieved_date between '...
1 vote
1 answer
1k views

Need to query records based on Date only (Sample Query given)

sSQL = " SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, ordHandling, ordTotal, ordDiscount, ordPrivateStatus FROM orders WHERE (ordStatus = 7) " I want to display orders that ...
43 votes
3 answers
23k views

What does the word "SARGable" really mean?

SQL Server users use the term "sargable". I'm wondering if there is an objective implementation-agnostic timeless definition for "sargable." For instance, WHERE foo LIKE '%bar%' is said by many to be ...
3 votes
2 answers
40k views

Should I join datetime to a date using cast or range?

This question is a take-off from the excellent one posed here: Cast to date is sargable but is it a good idea? In my case, I am not concerned with the WHERE clause but in joining to an events table ...
7 votes
2 answers
16k views

Speed up an aggregate query on an 11 million row table

I have a query that I want to speed up: SELECT sum(case when FlagDTD = 1 then Success else 0 end) as SuccessDTD , sum(case when FlagDTD = 1 then [Error] else 0 end) as ErrorDTD , round(sum(...
12 votes
3 answers
24k views

Performance hit using CAST in T-SQL

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). If myField is of type NVARCHAR, we can ...
4 votes
1 answer
319 views

How can I improve speed of select?

I have a table set up like this: The DB has 10B rows. CREATE TABLE [Tick].[X_H]( [utcDT] [datetime2](7) NOT NULL, [Symbol] [nvarchar](50) NOT NULL, [Bid] [float] NULL, [Ask]...
2 votes
3 answers
4k views

10+ minute query time with DATEPART in WHERE clause

I have a list of records with datetimes over more than 1 year. My goal is to compare the count of today's records with the count of records from last year, where the week # and day of the week from ...
5 votes
2 answers
14k views

Optimized date compare in WHERE clause, convert or datediff=0?

In my query I am comparing two dates in the WHERE clause once using CONVERT: CONVERT(day,InsertedOn) = CONVERT(day,GETDATE()) and another using DATEDIFF: DATEDIFF(day,InsertedOn,GETDATE()) = 0 Here ...
1 vote
0 answers
113 views

Most Efficent way WHERE [Date] = Today [duplicate]

Was wondering what would be the most efficient way to get data from today, does converting the date something like WHERE CONVERT(CHAR(8),[DateTimeColumn],112) = CONVERT(CHAR(8),GETDATE(),112) Not ...