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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Pratima PharandeCommented:
SELECT *  Where convert(varchar,dateAdded,111) = convert(varchar,getdate(),111)
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
brejkCommented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.