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

vbnetcoder
vbnetcoder used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
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*/
Olaf DoschkeSoftware Developer

Commented:
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 Expert
Awarded 2016
Top Expert 2016

Commented:
@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.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Expert
Awarded 2016
Top Expert 2016

Commented:
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*/

Author

Commented:
ty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial