October 18, 2010 at 1:18 pm
I need to compare whether a particular date(say date1) is greater than date2...How do i do it in SQL
October 18, 2010 at 1:24 pm
Sandy2704 (10/18/2010)
I need to compare whether a particular date(say date1) is greater than date2...How do i do it in SQL
What data types are used to store your dates?
What have you tried so far?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 18, 2010 at 1:56 pm
I tried working in the following way
( @startDate<=TerminationDate and @endDate <= TerminationDate )
(I m passing @startDate and @endDate as parameters)
TerminationDate is DateTime datatype
@startDate and @endDate are DateTime datatype
October 18, 2010 at 2:01 pm
Sandy2704 (10/18/2010)
I tried working in the following way( @startDate<=TerminationDate and @endDate <= TerminationDate )
(I m passing @startDate and @endDate as parameters)
TerminationDate is DateTime datatype
@startDate and @endDate are DateTime datatype
Can you be more specific about what you're trying to do? Can you provide some examples with real dates indicating what your expecting and what you're getting?
The syntax you provided is valid, but without the examples it's hard to say what the problem is.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 18, 2010 at 3:40 pm
select distinct pid
from table1 det with (nolock)
inner join table2 rate with (nolock) on rate.id = det.SID
where providerid = 2673 and rate.Fyr = '09-10' and (TerminationDate is null) or ( '04/11/2010'<=TerminationDate) and '04/30/2010' <= TerminationDate) ))
and det.procId = 85
this query checks whether the termination date is greater than startdate & end date....it gives me the pid values.....(here is the issue it gives me pid values even if the terminationdate is 04/11/2005)....Couldn't figure out the issue???
October 18, 2010 at 3:41 pm
both start date, end date and termination date are datetime datatype
October 18, 2010 at 3:49 pm
Sandy2704 (10/18/2010)
select distinct pidfrom table1 det with (nolock)
inner join table2 rate with (nolock) on rate.id = det.SID
where providerid = 2673 and rate.Fyr = '09-10' and (TerminationDate is null) or ( '04/11/2010'<=TerminationDate) and '04/30/2010' <= TerminationDate) ))
and det.procId = 85
this query checks whether the termination date is greater than startdate & end date....it gives me the pid values.....(here is the issue it gives me pid values even if the terminationdate is 04/11/2005)....Couldn't figure out the issue???
The query you posted will not work. There's 2 more ")" than "(".
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 18, 2010 at 4:03 pm
select distinct pid
from table1 det with (nolock)
inner join table2 rate with (nolock) on rate.id = det.SID
where providerid = 2673 and rate.Fyr = '09-10' and (TerminationDate is null) or ( '04/11/2010'< TerminationDate) and ('04/30/2010' <TerminationDate) and det.procId = 85
Forgot about braces....i was playing around and couldn't post the exact query
query executes without syntax error but the only issue is the below filtering doesn't work
(TerminationDate is null) or ( '04/11/2010'< TerminationDate) and ('04/30/2010' <TerminationDate)
even if the TerminationDate is 04/11/2005 it gives the pid corresponding to that TerminationDate
October 18, 2010 at 4:08 pm
Sandy2704 (10/18/2010)
(TerminationDate is null) or ( '04/11/2010'< TerminationDate) and ('04/30/2010' <TerminationDate)even if the TerminationDate is 04/11/2005 it gives the pid corresponding to that TerminationDate
It's because of the funkiness of priorities between and/or. Whichever's first, gets priority.
Use:
(TerminationDate is null) or (( '04/11/2010' < TerminationDate) and ('04/30/2010' < TerminationDate) )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 18, 2010 at 4:40 pm
You are probably not getting the results you are expecting because AND comes above OR in the operator precedence table. I've added brackets to show how your WHERE clause is actually filtering the data.
where (
(providerid = 2673)
and (rate.Fyr = '09-10')
and (TerminationDate is null)
)
or
(
(('04/11/2010' < TerminationDate) and ('04/30/2010' < TerminationDate))
and (det.procId = 85)
)
You were probably wanting something like this instead.
where (providerid = 2673)
and (rate.Fyr = '09-10')
and (
(TerminationDate is null)
or
(('04/11/2010' < TerminationDate) and ('04/30/2010' < TerminationDate))
)
and (det.procId = 85)
Also, does your TerminationDate column store a time component as well as the date component? If so, you might not get the results you are expecting if the TerminationDate is on the same day as the @startDate or @endDate parameters.
Finally, are you aware that the NOLOCK table hint can sometimes give "spurious" results?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply