Linked Questions

3 votes
5 answers

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 ...
Fajela Tajkiya's user avatar
21 votes
4 answers

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 ...
lecuong92's user avatar
  • 283
43 votes
3 answers

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 ...
Evan Carroll's user avatar
  • 63.7k
12 votes
3 answers

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 ...
user avatar
2 votes
3 answers

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 ...
ac927's user avatar
  • 23
7 votes
2 answers

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(...
homerj742's user avatar
5 votes
2 answers

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 ...
Kazi Lakit's user avatar
3 votes
2 answers

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 ...
Matthew's user avatar
  • 1,682
2 votes
2 answers

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 ...
Kirk Saunders's user avatar
2 votes
2 answers

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 '...
user109039's user avatar
0 votes
2 answers

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 ...
JustinDoesWork's user avatar
4 votes
1 answer

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]...
ManInMoon's user avatar
  • 345
1 vote
1 answer

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 ...
Robert Brady's user avatar
1 vote
0 answers

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 ...
JPVoogt's user avatar
  • 223