Ask The DBAs

Q) Query that returns rows in date range or overlapping date range

Question

I need a query returns all rows between two dates or rows that overlap the date range in any way.

Answer

SELECT *
FROM MyTable
WHERE
(
/*When the start date is before the range but the end date falls in or after the range*/
    (StartDate < @StartDate AND EndDate >= @StartDate)
/*When the StartDate falls in the range.  The end date can be in or out of the range*/
    OR (StartDate >= @StartDate AND StartDate < @EndDate)

)

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.

Q) How in SQL Server can I get a date value without any time value?

Question

How in SQL Server can I get a date value without any time value?

Answer

In SQL Server 2008 Microsoft introduced the DATE data type so you can simply do the following:

SELECT CAST(GETDATE() as DATE)

In SQL Server 2005 and below the DATETIME data type always retains a time component.  If you are not interested in time then commonly you set this to 00:00:000.  This will ensure sorting and aggregations on the “date” component are consistent.  A quick and easy way to convert a datetime to a standardized date is to do the following:

SELECT CAST(CONVERT(VARCHAR(50),GETDATE(),112) as DATETIME)

This has the benefit in that it results in a proper date time value (rather than a string) and is also deterministic so can be used in indexed views etc.

All Answers provided are subject to our standard Answers Disclaimer.

Q) When running a Reporting Services report with a Chart in it I get a “Method not found” error. What is the cause of this?

Question

When running a Reporting Services Report I get the following error:

An error has occurred during rendering of chart chart1. Details: Method not found: 'Boolean Dundas.Charting.WebControl.Series.get_PlotAsLine()'. (rsErrorDuringChartRendering)Method not found: 'Boolean Dundas.Charting.WebControl.Series.get_PlotAsLine()'.

What is the cause and fix of this?

Answer

This is likely to have been caused by corruption of the DunasWebChart.dll file located in the ReportServ\bin folder.  What caused this corruption is unclear, it may have resulted from manually copying on older/newer version of the DLL overtop of the correct DLL.To resolve this issue requires replacing the DLL with the correct version.  Reapplying the current SQL Server service pack does not appear to replace this file in our testing.  Instead you may need to backup and then reinstall SQL Server Reporting Services.

NOTE: Reinstalling Reporting Services may cause you to lose current configuration/and or data.  The best method of reinstalling Reporting Services without loss is outside the scope of this answer and further advice should be sought from a qualified professional.

All Answers provided are subject to our standard Answers Disclaimer.

Welcome to AskTheDBAs.com.  This is a community site where the SQL Server DBA team at RockSolid SQL will attempt to answer questions posed to them by the community.

Terms of use:  Questions may be rephrased or rewritten to be more general or to pose the question more clearly.  Not all questions will be answered.  Answers provided are general advice not nessecarily related to your original situation and are subject to our disclaimer.  Professional assistance should be sort to determine if the general advice provided is relevant to your specific situation.

NOTE: We are SQL Server DBAs so only questions relating to SQL Server will be answered!

To ask a question please email askthedbas@rocksolidsql.com or tweet the question to @askthedbas. 

Kind Regards

The RockSolid SQL DBA Team

Disclaimer

All answers given are general answers and may or may not apply to your own specific situation.  We recommend that you seek your own professional advice before deciding on the suitability of any answers provided on this site.  If you choose to follow any answers on this site you do so at your own risk.  While all care is taken to ensure answers are accurate, we do not make any guarantees about the suitability of advice and advice given may contain technical inaccuracies or topographical errors.  The answers provided are our own opinions and may differ from advice provided by Microsoft and/or other SQL Server professionals.

We always recommend that testing of an answer first be carried out in a suitable test environment before applying to any production environment.  We also recommend that you have full and complete backups of any database system before carrying out any type of change or modification.  In no event shall we be liable for any special, incidental, indirect, economic or consequential damages or for loss of profit, revenue or data howsoever caused, regardless of whether we could foresee or was advised of the possibility or likelihood of such loss or damage.