Find the system date and select where

tgra
tgra used Ask the Experts™
on
Hello,

In a view a want to select every rows that have the date = current date.  How do I do this in a WHERE select?

I want to perform something like this:

"

Select * from Order
Where "SystemDate" = Order.Orderdate

"
How do I get the "SystemDate" ?

Thanks for any suggestions!

Best regards,

Thomas...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Select * from Order
Where getdate() = Order.Orderdate
emoreau's query will only return the record that has the exact datetime stamp of when the query is run, you need:

select order.*
from order
where convert(varchar(10),order.orderdate,102) = convert(varchar(10),getdate(),102)

strips off the time ...

Author

Commented:
When I register new postings to the order table and use the smalldatetime it savs postings like this: "2002-11-21 00:00:00:000".  But when I use gettime() it fills out the 2002-11-21 xx:xx:xx:xxx with the systemclock.  When I then use the WHERE gettime() = order.orderedate it finds nothing. How to I avoid this (checking just the date and not the time).

I believe it has something to do with the formating?

Should I use smalldate(8) when creating the table to avoid saving the systemclock?

Thank you very much!

Best regards,

Thomas..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks you very much thomasdoods for the information for stripping of the time.  I beleive this solves me problem.

Thank you everybody!

Best regards,

Thomas...
glad I could help ...

enjoy

Commented:
tgra : you could give the points to tgra for that by accepting tgra answers ^_^

Commented:
there we go ^_^ thanks for coming back to the thread ^_^

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial