compare today to getdate() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare today to getdate()

Author

Topic

cray
Starting Member

9 Posts

Posted - 2006-01-13 : 09:48:39

I need a where clause like
Where field=getdate()
but I can't get it to work because the field is datetime so there is never an exact match. Do I need to break out each part just to compare today's date to the 'date' of getdate()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 09:50:24

select dateadd(day, 0, datediff(day, 0, getdate()))
or
select dateadd(day, datediff(day, 0, getdate()), 0)

-----------------
'KH'
if you can't beat them, have someone else to beat them

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 09:54:18

Select columns from yourTable where filed >=dateadd(day, datediff(day, 0, getdate()), 0) and filed<dateadd(day, datediff(day, 0, getdate()), 1)

or

Select columns from yourTable where Datediff(day,field,getdate())=0

Madhivanan

Failing to plan is Planning to fail

cray
Starting Member

9 Posts

Posted - 2006-01-13 : 10:01:25

Select columns from yourTable where Datediff(day,field,getdate())=0

The above worked great!

Thanks!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 10:05:33

Both will work correctly

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 10:08:49

"Select columns from yourTable where Datediff(day,field,getdate())=0"

Note that this won't use any index that you may have on "field", so you might prefer to use Madhi's first suggestion:


Select columns 
from yourTable 
where     field >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
      AND field <  DateAdd(Day, DateDiff(Day, 0, GetDate()), 1)

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 10:52:19

Is it Ok to use the following way?
I mean work? efficient?

Select columns from yourTable where field = CONVERT(varchar, GETDATE(), 101)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 10:54:13

quote:
Originally posted by Srinika

Is it Ok to use the following way?
I mean work? efficient?

Select columns from yourTable where field = CONVERT(varchar, GETDATE(), 101)

Not recommended

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 10:54:57

If "field" contains TIME, as well as DATE, then you won't get a match.

If "field" just contains DATE then you can do:

columns from yourTable where field = DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)

The rather horrid syntax of "DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)" is the most efficient way that I have found of stripping off the time component of a date/time.

Kristen

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 11:02:39

Kristen,

how about
Select columns from yourTable where CONVERT(varchar, field, 101) = CONVERT(varchar, GETDATE(), 101)

I'm worried about the complexity of the one that u suggesting and its several functions. won't that make it more time consuming?
[I'm asking just for my knowledge - not challenging u - I may be a kid here]

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-13 : 11:07:21

SELECT columns FROM table WHERE datefield >= convert(varchar(20),GETDATE(),101)

Kristen, what performace difference this will make? Maybe answered in this forums somewhere but there is no easy way to search..I have seen the above code several times from other developers but not sure Thanks in advance..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-13 : 11:30:11

In general, when you are selecting for a datetime range, it should be in this form:


where
	MyDateTime >= StartDateTime and
	MyDateTime <  EndDateTime

This is the most efficient way to do a test for a time range in SQL Server, especially when there is an index on the column. If you force it to evaluate a function for each row, it takes procesing time for the function calls, and it prevents SQL Server from using an index on that column. The functions in the code below are only evaluated once for the query, instead of once for each row, so it is much more efficient.


where     field >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
      AND field <  DateAdd(Day, DateDiff(Day, 0, GetDate()), 1)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-01-16 : 05:32:54

What MVJ said, basically.

"Select columns from yourTable where CONVERT(varchar, field, 101) = CONVERT(varchar, GETDATE(), 101)"

Nope, every row in YourTable will have to have "field" converted to varchar before comparison can begin. (Might still use the index, but it will have to scan the whole lot rather than using the appropriate "slug" of the index, AFAIK)

"SELECT columns FROM table WHERE datefield >= convert(varchar(20),GETDATE(),101)"

Nope (2) !! Not quite sure how SQL will see this one - two possibilities, 1) "datefield" will have to be converted to varchar before the comparison is made (full scan of whole table, or index, required) or 2) "convert(varchar(20),GETDATE(),101)" will have to be converted back to DateTime datatype first, and then a comparison made; this will be in native DateTime datatype, so will be efficient.

I expect that (2) would be used, however, I would want to be unambiguous to make sure that SQL does it "right", and to achieve that make sure that you have the same datatype on both sides of the comparison (a good Best Practice anyway). So you could do:

WHERE datefield >= CONVERT(DateTime, convert(varchar(20),GETDATE(),101))

but that's basically where I came in! Admittedly the syntax of my "truncate the time stuff off" looks horrid, but its proven to be the fastest method for SQL server.

If you find it hard to read (or others will who come along behind you) just put a comment as to how it works.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-16 : 06:29:42

>>I'm worried about the complexity of the one that u suggesting and its several functions

Dont omit anything just because it is complex

In Selecting date column when you use Convert function then the Date actually becomes Date_Like_varchar so that you may not use it for comparision if any

Madhivanan

Failing to plan is Planning to fail

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-17 : 16:44:06

Thanks! Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources