Solved

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

Posted on 2014-12-11
10
157 Views
Last Modified: 2014-12-11
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)
and
 12/14/2014 23:59:59  (three days from day)
0
Comment
Question by:maqskywalker
  • 6
  • 3
10 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40495312
try

WHERE datediff(day, 0, [FromDate]) >= datediff(day, 0, getdate())) AND ([FromDate] <= datediff(day, 3, getdate())
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40495320
DateDiff returns an integer,days elapsed since Jan 1, 1900
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 250 total points
ID: 40495355
correction...

WHERE datediff(day, 0, [FromDate]) >= datediff(day, 0, getdate()) AND datediff(day, 0, [FromDate]) <= datediff(day, 3, getdate())
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40495358
>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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40495389
Hi,
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) )
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40495399
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.

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40495418
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.
http://sqlmag.com/t-sql/t-sql-best-practices-part-2
Itzik Ben-Gan
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40495450
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.

Regards,

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40495456
:)

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.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40495518
Thank you.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 54
Query Peformance + mulitple query plans 9 55
Get Duration of last Status Update 4 35
SQL Server 2012 r2 Make faster Temp Table 17 105
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question