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

Question

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.

score 3 · Accepted Answer · answered Jun 10 '09 at 20:21

3

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

answered Jun 10 '09 at 20:21

Joel Coehoorn

399,467
113
570
794

2

The quickest way is a datediff/dateadd method http://stackoverflow.com/questions/133081/most-efficient-way-in-ms-sql-to-get-date-from-datetime/150722#150722 I've seen similar test in SQL Server mag by Itzhak – gbn Jun 11 '09 at 05:11

score 1 · Answer 2 · answered Jun 10 '09 at 20:24

1

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

answered Jun 10 '09 at 20:24

Chris Van Opstal

36,423
9
73
90

I'm afraid it will work only before 12:00 PM DATEDIFF rounds the difference – tekBlues Jun 10 '09 at 20:26
DateDiff should add one every time it crosses a boundary for the datepart. – Joel Coehoorn Jun 10 '09 at 20:34
Nice, but sureyl should be >= 0. Also applying a function to the table column means any index won't be used so over many rows this will be slow. – gbn Jun 11 '09 at 05:13

score 0 · Answer 3 · answered Jun 10 '09 at 20:24

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

score 0 · Answer 4 · answered Jun 11 '09 at 06:44

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

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

4 Answers4