compare dates

I have a query like this

Select * from Table
where datecreated = CURRENT_TIMESTAMP

What is the best way to update this query so it only looks at date and not the time?
vbnetcoderAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
where datecreated >= CAST(CURRENT_TIMESTAMP AS date) AND datecreated < DATEADD(DAY, 1, CAST(CURRENT_TIMESTAMP AS date))
1
 
xiao jinshouCommented:
Select * from Table
 where datecreated = DATEPART ( d , getdate() )
0
 
John TsioumprisSoftware & Systems EngineerCommented:
select * from Table where cast(DateInserted as Date) = cast(Current_TimeStamp as Date)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Russ SuterCommented:
The "best way" is to store the date information without the time. Any functions you execute in the WHERE clause will slow things down due to function call overhead and/or lack of indexing.

You might want to consider adding a computed column to your table and then indexing on that column. If you're doing these sorts of queries frequently or on large sets of data you'll find performance will be greatly improved using this approach.
0
 
Scott PletcherSenior DBACommented:
The best way is to avoid using any function on a table column; otherwise you make the condition non-sargable.  Functions on literals/static values don't matter.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If the datecreated column is a date only data type then you don't need to do nothing as it will truncate the time and will store only the date part.
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.