We help IT Professionals succeed at work.

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.
Comment
Watch Question

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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

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