how do i return records from a table where the field create date (date time) is equal to todays date?

how do I return records from a table where the field create date (date time)  is equal to todays date?  I can't hardcode todays date so I will need to use getdate() or something.
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Pawan KumarDatabase ExpertCommented:
You can use one of the below -

SELECT * FROM yourTableName
WHERE yourDateColumn = CAST(GETDATE() AS DATE)  /*If you are using DATE column*/

OR

SELECT * FROM
yourTableName
WHERE yourDateColumn > CAST(GETDATE() AS DATE) AND yourDateColumn  <  CAST(GETDATE() + 1 ) AS DATE)  /*If you are using DATETIME column*/

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
Olaf DoschkeSoftware DeveloperCommented:
or the third and  fourth possibility working anyway:

SELECT * FROM yourTableName
WHERE CAST(yourDateColumn as DATE) = CAST(GETDATE() AS DATE)

Open in new window


or

SELECT * FROM yourTableName
WHERE DATEDIFF(day,GetDate(),yourDateColumn) = 0

Open in new window


Bye, Olaf.
Pawan KumarDatabase ExpertCommented:
@Olaf-

Both your suggestion will lead to scanning the entire table. This because you are using functions around the column and that becomes them non - sargable.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
WHERE create_date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
    create_date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)

Key points:
1) The method above is the standard, "best practice" approach to adjust a date time to a given boundary.  This method has proven to be very efficient and very flexible, and thus can be used consistently for other day ranges as well.  For example, if you wanted everything for the current month rather than for the current day, the only part of the code that has be changed are the date intervals:
WHERE create_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    create_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

2) Don't perform any function on a table column if it's at all possible to avoid.  Instead, do as much manipulation as you need of any static values in the WHERE (or JOIN) conditions.

3) Use >= and <, not BETWEEN/>= & <=, because that may cause data to be missed if the data type of the column changes.
Pawan KumarDatabase ExpertCommented:
A minor update for my first comment -

SELECT * FROM yourTableName
WHERE yourDateColumn = CAST(GETDATE() AS DATE)  /*If you are using DATE column*/

OR

SELECT * FROM
yourTableName
WHERE yourDateColumn >= CAST(GETDATE() AS DATE) AND yourDateColumn  <  CAST(GETDATE() + 1 ) AS DATE)  /*If you are using DATETIME column*/
vbnetcoderAuthor Commented:
ty
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
SQL

From novice to tech pro — start learning today.