• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

t-sql query add time to getdate function in where clause

i'm using sql server 2008 R2.

I have a table with a smalldatetime column called FromDate

I want to filter FromDates that are between today's date and 3 days from today.

Today's date is 12/11/2014.

Right now my where clause looks like this:

WHERE ([FromDate] >= getdate()) AND ([FromDate] <= dateadd(days, 3, getdate()) )

But that doesn't take time into consideration.

how do I revise my where clause to to include 00:00:00 to today's date and 23:59:59 to 3 days from today.

So then my where clause would be filtering between

12/11/2014 00:00:00  (today's date)
 12/14/2014 23:59:59  (three days from day)
  • 6
  • 3
2 Solutions
Mike EghtebasDatabase and Application DeveloperCommented:

WHERE datediff(day, 0, [FromDate]) >= datediff(day, 0, getdate())) AND ([FromDate] <= datediff(day, 3, getdate())
Mike EghtebasDatabase and Application DeveloperCommented:
DateDiff returns an integer,days elapsed since Jan 1, 1900
Mike EghtebasDatabase and Application DeveloperCommented:

WHERE datediff(day, 0, [FromDate]) >= datediff(day, 0, getdate()) AND datediff(day, 0, [FromDate]) <= datediff(day, 3, getdate())
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>12/11/2014 00:00:00  (today's date)  and  12/14/2014 23:59:59  (three days from day)
This can also be interpreted as >= 12/11/2014 and < 12/15/2014

SQL expert PortletPaul addresses this in his article Beware of Between.  An excellent read, and if it helped you click on the green 'Was this article helped you?' button at the end.
I would address this a little differently.
Avoid use of functions on the data because this badly affects performance. (see: sargable ) i.e.

datediff(day, 0, [FromDate])

badly affects performance because indexes cannot be used and using that function can be removed.

All that is required is to set getdate() to the time 00:00:00 instead, that can be done in two ways:
cast(getdate() as date)      or
dateadd(day, datediff(day,0, getdate() ), 0)

so your where clause could be:

WHERE [FromDate] >= cast(getdate() as date) AND [FromDate]) < dateadd(day, 3, cast(getdate() as date) )
Mike EghtebasDatabase and Application DeveloperCommented:
BTW, earlier solution could be improved using BETWEEN

WHERE datediff(day, 0, [FromDate])  BETWEEN datediff(day, 0, getdate()) AND datediff(day, 3, getdate())

But performance-wise, as Paul stated, still will not be as good.

Yikes: don't use between for date ranges... almost guaranteed to get you into trouble

Do take the time to read my article "Beware of Between" or
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

I have promised myself to read the link "Beware of Between" thoroughly in a day or two because it will be very educational for me.

That said, DateDiff returns an integer so between int1 and int2 is what is at work. the values are no longer dates. I appreciate some comment on that for my later reference.



Yes; between is OK for integers, but we return to my initial comment regarding performance of that approach.

My concern is that it is difficult for some to recognize that between is not appropriate for date ranges.
Mike EghtebasDatabase and Application DeveloperCommented:
Thank you.
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.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now