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

SQL Date with minimum time and like date with maximum time

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
SimonJohnG
Asked:
SimonJohnG
  • 2
  • 2
1 Solution
 
PortletPaulCommented:
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
 
SimonJohnGAuthor Commented:
Thank you for your help, works great.

Regards
Simon
0
 
SimonJohnGAuthor Commented:
Fantastic reponse time and clear and precise explaination. Thank you
0
 
PortletPaulCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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