Last post Jan 09, 2017 04:15 AM by wmec
Jan 02, 2017 01:03 PM|mjkhan|LINK
I am currently working on C# with Sql Server Language, trying to design a query where I have to select quarters(Q1, Q2, Q3, Q4).
My issue is that, suppose if the user from the front end selects only Q1 then I can set the query to date range for quarter Q1, and if the user selects Q1 and as well Q2 then I can still set the date range for Q1 and Q2 because they fall under sequence date
But if the user selects Q1 and Q3 then how do I prepare date range query(either in stored procedure or simple select query) and excecute for this.
I was trying with this but no use.
where CDate in(cdate between '2015-05-20' and '2015-06-01' and CDate between '2016-06-03' and '2016-06-04'
but I don't want to use temp table to store query data for saperate date ranges and then union that result. because I have different parameters with me as well.
Jan 02, 2017 07:19 PM|RichardY|LINK
Using BETWEEN can be a little more complicated than you might think. How to do it depends on whether the column is DATE or DATETIME. You can CAST the value to a DATE (if using Sql Server 2008+) to signal that you do not care about the time portion of the
date. You can also use DATEDIFF to determine if a date is before, on or after a given date. I typically don't bother with any of this and just do something like:
where (CDate >= '20150520' and CDate < '20150602') -- +1 day
OR (CDate >= '20160603' and CDate < '20160605') -- +1 day
I guess you know the dates used for comparison are not yearly quarters.
Jan 03, 2017 04:48 AM|mjkhan|LINK
where (CDate >= @Q1Start and CDate < @Q1End) -- +1 day
OR (CDate >= @Q2Start and CDate < @Q2End) -- +1 day
Jan 03, 2017 08:32 AM|Chris Zhao|LINK
You could write the query by c# for example.
StringBuilder mySql = new StringBuilder("SELECT ... ");
Jan 07, 2017 01:35 PM|RichardY|LINK
If you need to pass the values as parameters to a stored procedure and you are using Sql Server 2008 or later, you can use a Table Valued Parameter.
In your .Net code collect the date ranges in a DataTable. Then create a new SqlParameter which is of the "structured" type and set its value to the DataTable.
Follow the steps in the above link to declare the parameter in the stored procedure.
Jan 09, 2017 04:15 AM|wmec|LINK
Try to put
where cdate between '2015-05-20' and '2015-06-01' or CDate between '2016-06-03' and '2016-06-04'
for several date ranges and please remove "cdate in ...".