Solved

SQL Date with minimum time and like date with maximum time

Posted on 2013-06-19
4
269 Views
Last Modified: 2013-06-19
If I enter dates into a parameter and both dates are the same how can I make one of the dates the minimum value for that date so 00:00 and the end date 23:59.

so
BETWEEN @StartDate AND @EndDate

I know its likely to be simple but I just cant figure it.

Regards
S
0
Comment
Question by:SimonJohnG
  • 2
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39259503
don't use between

use this instead

[field] >= @StartDate AND [field] < dateadd(day,1,@EndDate)

so, if the parameters are both '2013-06-01'

this equates to:
[field] >= '2013-06-01' AND [field] < dateadd(day,1,'2013-06-01') -- i.e. '2013-06-02'

If the parameters are '2013-06-01' & '2013-06-30'

this equates to:
[field] >= '2013-06-01' AND [field] < dateadd(day,1,'2013-06-30') -- i.e. '2013-07-01'

avoiding "between" is the solution.
for more on this topic please see: "Beware of Between"
0
 

Author Comment

by:SimonJohnG
ID: 39259572
Thank you for your help, works great.

Regards
Simon
0
 

Author Closing Comment

by:SimonJohnG
ID: 39259580
Fantastic reponse time and clear and precise explaination. Thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39261034
and thank you! Quick closures is very much appreciated too.

Do take be cautious of using 'between' when filtering by date ranges, the >= with < construct is usually a more reliable method (You just need to get used to the idea that the higher date is 'the next day'). Do read that article if you need more.

Cheers, Paul
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

     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.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now