SQL SELECT Statement Where dateAdded >= GETDATE() - 1 - how to modify this to Just TODAY?

SQL SELECT *  Where dateAdded >= GETDATE() - 1   -  how to modify this to Just TODAY?

I have the following query above which returns all records in the last 24 hours. I would like to learn how to modify this query to return only those items that have a dateAdded of TODAY

so if today is 1/31, I want all stories with dateAdded = 1/31, not those items that are within 24 hours the current GETDATE.

Ideas? Thanks
nverfollowttAsked:
Who is Participating?
 
brejkConnect With a Mentor Commented:
@nverfollowtt

I have already written an explanation to my code - the first answer for your question. But let me present a similar example based on AdventureWorks sample database (this time it's not about getting one day dates but one year dates):

USE AdventureWorks
GO

-- create an index on date column
CREATE INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate)

-- for the statements below see the execution plans (press Ctrl+M before executing)

-- this will perform Index Seek
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20040101' AND OrderDate < '20050101'

-- this will perform Clustered Index Scan
SELECT OrderDate FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2004

-- clean up
DROP INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader

You situation is very similar - if you put dateAdded column into any scalar function (DATEADD, DATEDIFF, ...) query optimizer will never use an index because it will have to calulate the expression for each row in the table. That's why I suggest to use my code (see the snippet for two options).

-- use this one if you are interested in 
-- all dates and times from TODAY (even in the future)
SELECT * 
FROM YourTable 
WHERE dateAdded >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) 
AND dateAdded < DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) + 1
 
-- use this one if you are interested in
-- all dates and times from TODAY (but not in the future)
SELECT * 
FROM YourTable 
WHERE dateAdded >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) 
AND dateAdded <= GETDATE()

Open in new window

0
 
brejkCommented:
SELECT * FROM YourTable WHERE dateAdded >= DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) AND dateAdded < DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) + 1
0
 
HuyBDCommented:
try this
SELECT Statement Where datediff(h,dateAdded,GETDATE()) between 0 and 24

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pratima PharandeCommented:
SELECT *  Where convert(varchar,dateAdded,111) = convert(varchar,getdate(),111)
0
 
ee_rleeCommented:
WHERE DATEADD(day, DATEDIFF(day, 0, dateadded), 0) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
0
 
brejkCommented:
But you must remember that when you put dateAdded column in any expression with use of scalar functions SQL Server's optimizer will never use the index on dateAdded column. That's why I did not put the column into any expression. All expressions in my query are calculated separately and then dateAdded column is searched within the given range.
0
 
bradleys40Commented:
Am i Missing something sureley the query should be

select *
from table
Where dateAdded = GETDATE()
0
 
brejkCommented:
@bradleys40

No. GETDATE() will return current date and _TIME_.
0
 
bradleys40Commented:
thought i was missing something fair point brejk
0
 
nverfollowttAuthor Commented:
Thank you everyone for your input. With some many different solutions above, how do I know which is the smartest most efficient way to implement?
0
 
nverfollowttAuthor Commented:
Any ideas? 3 different methods. how does one pick the right smartest method?
0
 
HuyBDCommented:
I believe that all solutions return difference
You can specify which solution is work
if you get records have date in last 24 hours, try my solution
if you get records have date in last but in current date , try this
SELECT Statement Where datediff(day,dateAdded,GETDATE())=0 and dateAdded < GETDATE()

Open in new window

0
All Courses

From novice to tech pro — start learning today.