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

or the third and  fourth possibility working anyway:

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

Select allOpen in new window

or

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

Select allOpen in new window

Bye, Olaf.

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

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.

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

ty