June 9, 2016 at 11:02 am
In SSRS normally I would do a Date Parameter Statement like such:
Select
ek.effdate As StartD
ek.effdate As EndD
Where
ek.effdate between @StartD and @EndD
But I need something to work along the lines of the below statement
which uses two different columns for the date range:
Select
ek.effdate As StartD
ek.termdate As EndD
Where
ek.effdate @StartD between ek.termdate @EndD
June 9, 2016 at 11:04 am
SilverBack (6/9/2016)
In SSRS normally I would do a Date Parameter Statement like such:
Select
ek.effdate As StartD
ek.effdate As EndD
Where
ek.effdate between @StartD and @EndD
But I need something to work along the lines of the below statement
which uses two different columns for the date range:
Select
ek.effdate As StartD
ek.termdate As EndD
Where
ek.effdate @StartD between ek.termdate @EndD
Would you care to explain what you are trying to accomplish? Your second SQL statement makes no sense.
June 9, 2016 at 11:06 am
I thought I did. I am trying to find a way to use a Between statement with two different columns. The database I am working
with is highly encrypted and fractured. So a lot of fields are duplicated but different in such minor ways and must be for regulation
Lynn Pettis (6/9/2016)
SilverBack (6/9/2016)
In SSRS normally I would do a Date Parameter Statement like such:
Select
ek.effdate As StartD
ek.effdate As EndD
Where
ek.effdate between @StartD and @EndD
But I need something to work along the lines of the below statement
which uses two different columns for the date range:
Select
ek.effdate As StartD
ek.termdate As EndD
Where
ek.effdate @StartD between ek.termdate @EndD
Would you care to explain what you are trying to accomplish? Your second SQL statement makes no sense.
June 9, 2016 at 11:11 am
SilverBack (6/9/2016)
I thought I did. I am trying to find a way to use a Between statement with two different columns. The database I am workingwith is highly encrypted and fractured. So a lot of fields are duplicated but different in such minor ways and must be for regulation
Lynn Pettis (6/9/2016)
SilverBack (6/9/2016)
In SSRS normally I would do a Date Parameter Statement like such:
Select
ek.effdate As StartD
ek.effdate As EndD
Where
ek.effdate between @StartD and @EndD
But I need something to work along the lines of the below statement
which uses two different columns for the date range:
Select
ek.effdate As StartD
ek.termdate As EndD
Where
ek.effdate @StartD between ek.termdate @EndD
Would you care to explain what you are trying to accomplish? Your second SQL statement makes no sense.
This, ek.effdate @StartD between ek.termdate @EndD , makes no sense. What are you trying to accomplish with this comparison?
June 9, 2016 at 11:16 am
This is a date between statement do you read I said I am looking for a way to do something like this? I have
no idea if it possible? I need a Multi-Column between statement for a Parameter as previously stated.
June 9, 2016 at 11:25 am
Would this article give you the answer you're looking for?
http://www.sqlservercentral.com/articles/T-SQL/105968/
It mentions month, but should work for any range.
June 9, 2016 at 11:27 am
It looks like you want to use 1 of 2 end date values. 1 from the query, 1 from a variable.
Any rules? Like use the lowest value? Or only use the second one if the first is blank/missing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2016 at 11:32 am
Again, to reiterate what Lynn is saying, please explain what you are trying to do without providing code.
The syntax you provided makes no sense. We cannot understand it.
If you want one date field to be greater than or equal to a variable, and a second date field less than or equal to a variable, BETWEEN will not work.
You will need to use different syntax, if that is in fact what you are trying to do.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 9, 2016 at 11:45 am
SilverBack (6/9/2016)
This is a date between statement do you read I said I am looking for a way to do something like this? I haveno idea if it possible? I need a Multi-Column between statement for a Parameter as previously stated.
Yes, I read what you posted. You still haven't explained what you are trying to accomplish. The "code" posted doesn't work nor does it clearly explain what you are trying to accomplish.
Given a well described set of requirements I am quite capable of helping you with a solution to this problem.
June 9, 2016 at 11:46 am
IKm looking for two columns to in SSRS as Parameters.
So @StartD needs to be a column A in a Table and @EndD needs to be Column B in the same table.
So My Table is EK. and my columns for dates are StartD and EndD and I need them to function using the BETWEEN command in SSRS allowing
the user to pick the date range using a Date Picker.
June 9, 2016 at 12:00 pm
This still is not really very clear what you are trying to do but here is my shot in the dark. You are looking for rows where BOTH ColA AND ColB are between your 2 date parameters?
The code for something like that would be:
WHERE ek.effdate between @StartD AND @EndD
AND ek.termdate between @StartD AND @EndD
Is that what you are looking for?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2016 at 12:00 pm
SilverBack (6/9/2016)
IKm looking for two columns to in SSRS as Parameters.So @StartD needs to be a column A in a Table and @EndD needs to be Column B in the same table.
So My Table is EK. and my columns for dates are StartD and EndD and I need them to function using the BETWEEN command in SSRS allowing
the user to pick the date range using a Date Picker.
Okay, is this column A and column B are in a different table or the table EK? Again, you really aren't explaining what you are trying to accomplish. I feel like I am pulling hens teeth to get to the bottom of what you are trying to accomplish.
Please, forget everything you know about what you are asking, then read what you have posted. Does it make sense if you eliminate the context that you understand. Remember, we are volunteers, we don't have access to your systems, we can't see what you see. We depend on you providing us with what we need to know to understand your environment and requirements in order to help you and answer your question(s). We are not mind readers nor do we possess some magical means of divining what you are trying to accomplish.
June 9, 2016 at 12:02 pm
SilverBack (6/9/2016)
IKm looking for two columns to in SSRS as Parameters.So @StartD needs to be a column A in a Table and @EndD needs to be Column B in the same table.
So My Table is EK. and my columns for dates are StartD and EndD and I need them to function using the BETWEEN command in SSRS allowing
the user to pick the date range using a Date Picker.
Ok, did you read what I posted?
You cannot use a between in this manner.
You need to do:
Field1 >= @Parameter1 and Field2 <= @Parameter2
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 9, 2016 at 12:30 pm
Sean Lange (6/9/2016)
This still is not really very clear what you are trying to do but here is my shot in the dark. You are looking for rows where BOTH ColA AND ColB are between your 2 date parameters?The code for something like that would be:
WHERE ek.effdate between @StartD AND @EndD
AND ek.termdate between @StartD AND @EndD
Is that what you are looking for?
Looks like you found your answer. To bad you couldn't just have explained it that way to begin with, things would have been so much easier.
Congrats, Sean, on your shot in the dark. Looks like we know who had the crystal this time.
June 9, 2016 at 12:34 pm
Lynn Pettis (6/9/2016)
Sean Lange (6/9/2016)
This still is not really very clear what you are trying to do but here is my shot in the dark. You are looking for rows where BOTH ColA AND ColB are between your 2 date parameters?The code for something like that would be:
WHERE ek.effdate between @StartD AND @EndD
AND ek.termdate between @StartD AND @EndD
Is that what you are looking for?
Looks like you found your answer. To bad you couldn't just have explained it that way to begin with, things would have been so much easier.
Congrats, Sean, on your shot in the dark. Looks like we know who had the crystal this time.
Just wanted to mention that it's logically equivalent to
WHERE ek.effdate >= @StartD
AND ek.termdate <= @EndD
As long as the date ranges are correct.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply