Selecting records that match the current date. 2

[ronaldmacdonald]

Hi

I am trying to select rows from a table where a date field match the current date.

The date is stored in the table as 'yyyy-mm-dd hh:mm:ss' so I am looking for something that would be the equivalent of select * from table where datefield = sysdate

 

[djj55]

If you are using 2008 you can use CAST AS DATE.

select * from table where CAST(datefield AS DATE) = CAST(sysdate AS DATE)

There are other ways to do it. Or you can change sysdate to YYYY/MM/DD and use >. By the way I usually use GETDATE().

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

SQL Server does not have a sysdate function. Oracle and other DBMS's do. Are you using Microsoft SQL Server?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ronaldmacdonald]

Yes sorry, I am more use to Oracle but this is for SQL Server 2005.

 

[simian336]

select *
from table
where DATEADD(DAY, 0, DATEDIFF(DAY, 0, datefield)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

GETDATE() is your sysdate equivant.

In 2005 your date field has the time incorperated in it. DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) will make the time all zeros.

Simi

 

[gmmastros]

Actually, the best way to do this is...

select *
from table 
where datefield >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
      AND DateField < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

Even though there are two conditions instead of just one, this query will perform better if there is an index on the DateField column.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ronaldmacdonald]

Hi simian336 - your statement worked perfectly on sql server - thankyou.
gmmastros - the field isn't indexed but its always good to consider efficiency!

 

[simian336]

Actually a little code I picked up for George a couple of years ago...

Thanks George.

Simi