How to get entries of current date portion from column with datetime as datatype (TSQL)

Question

Hi,

I have daily scheduled task that queries a table which includes column named AttemptDate with datetime as datatype. However the task queries all entries regardless of the date. So when the tasks executes, it outputs entries of all the dates as shown below:

 2009-06-06 06:01:30.852 
 2009-06-07 01:41:46.719 
 2009-06-08 03:58:23.945

The SQL query is shown below:

SELECT AttemptDate from dbo.ChangeAttempt

The table dbo.ChangeAttempt has the following structure:

Column           Data Type       Constraints        
------           ---------       -----------

EventData        xml             NOT NULL
AttemptDate      datetime        NOT NULL DEFAULT GETDATE()
DBUser           char(50)        NOT NULL

My question is: From my existing TSQL query, how do I get entries of the current date part if I add where clause?

What I mean by current date here is the date the TSQL query is scheduled to run.

ADDITION:

The SQL Server version I am running the TSQL query against is 2005.

Answer 1

SELECT AttemptDate 
FROM dbo.ChangeAttempt
WHERE AttemptDate >= cast(floor(cast(getdate() as float)) as datetime)

Answer 2

SELECT AttemptDate 
FROM dbo.ChangeAttempt
WHERE DATEDIFF(d, AttemptDate, GetDate()) = 0

Answer 3

If you are asking how do you get entries that were defaulted in by the constraint, then there isn't a way how the table is set up. If you omit the attemptdate, the server will default it in for you using the current date and afterwards its impossible to tell.. if you can alter the table structure, you could facilitate this by using a flag of some sort and a trigger

Answer 4

I think your query will perform better if you first save the current database datetime in a variable:

DECLARE @curr_date datetime;  
SET @curr_date = cast(floor(cast(getdate() as float)) as datetime)  
SELECT AttemptDate  
FROM dbo.ChangeAttempt  
WHERE AttemptDate >= @curr_date

Answer 5

See:

The ultimate guide to the datetime datatypes: Getting rid of the time portion by Tibor Karaszi