Help with SQL Query Regarding Dates

  • Hi All,

    I wonder if someone can help with an SQL query I need for an SSRS subscription.  The code is run against a school database which unfortunately lists dates of terms only and not the dates in between.

    Here is a screenshot of the table:

    TermDatesTable

    I need to run a weekly SSRS subscription which pulls the dates of the terms based on the date the subscription is run but if the subscription runs outside of the dates of a term the parameters are incorrect and the subscription becomes disabled.

    My Current code is as follows:

    SELECT
    CONVERT(varchar(17), MIN(txtStartDate), 111) AS start_date,
    CONVERT(varchar(17), MAX(txtFinishDate), 111) AS end_date
    FROM TblSchoolManagementTermDates
    WHERE (CONVERT(date, GETDATE()) BETWEEN txtStartDate AND txtFinishDate)

    I am wondering how best to get around this issue for a long term solution and wondered if anyone can think of a solution and suggest the SQL code to make this work.

    Many thanks in advance.

    Stuart

  • We need a bit more clarity on this, I think.

    Why are you storing start and finish dates as varchar() rather than DATETIME?

    What are the parameters, and why are they 'incorrect'? How do 'incorrect' parameters disable a subscription?

    Based on the data you have provided, what results are you hoping to see?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin wrote:

    We need a bit more clarity on this, I think.

    Why are you storing start and finish dates as varchar() rather than DATETIME?

    Or better yet, store them as datetime2(0), which is more efficient than datetime and would also prevent those pesky milliseconds from being stored. 🙂

  • kaj wrote:

    Phil Parkin wrote:

    We need a bit more clarity on this, I think.

    Why are you storing start and finish dates as varchar() rather than DATETIME?

    Or better yet, store them as datetime2(0), which is more efficient than datetime and would also prevent those pesky milliseconds from being stored. 🙂

    The DateTime vs DateTime2 argument is not as clear-cut as you imply.

    Take a look here, for example, to see some reasons DateTime may be preferred: https://www.sqlservercentral.com/forums/topic/why-do-some-dbas-avoid-datetime2

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin wrote:

    The DateTime vs DateTime2 argument is not as clear-cut as you imply.

    Take a look here, for example, to see some reasons DateTime may be preferred: https://www.sqlservercentral.com/forums/topic/why-do-some-dbas-avoid-datetime2

    Okay, that's an interesting discussion and certainly reveals some pitfalls.

    However, the main reason I suggested DATETIME2(0) was that the milliseconds which are always stored with DATETIME (and shown when doing a select) could have been the reason for the OP to have the dates stored as varchar. God knows I'm plenty irritated by seeing those when i do a simple select on a number of DATETIME columns. Most of the time they're irrelevant and just obscuring the result and taking up place. In those cases and for that reason I prefer DATETIME2(0).

    If nothing else I hope we can agree that both DATETIME and DATETIME2(0) are better choices than VARCHAR or CHAR when working with date+time.

    • This reply was modified 1 week, 5 days ago by  kaj. Reason: column -> columns (TYPO)
  • Dear All,

    Thanks for your responses.  I agree regarding the DateTime over varchar but unfortunately, it is a school management information system provided by a company so we have no control over the table or column formats.

    This query is embedded in a SSRS report and produces the parameters for the report to run.  In this case if the query was run today it would produce the parameters as shown below which is the start date and end date of the current term (row 1 of the screenshot in the original post).

    Parameter Output

    The master report then looks for detentions and merits etc which have been inputted during the school term that the report is run via these parameters.

    The SSRS subscription is disabling itself if the report/query runs during a school holiday period between the dates of 2 terms and a term cannot be found and therefore errors.  For example the Christmas holiday which takes place between term 1 and 2 of the table in the original post (2021-12-11 - 2022-01-05).  Annoying there is nowhere in the database that stores holiday information and the web-based front end application is coded to treat the dates in between this as a holiday period.

    I guess one solution would be to pull the start-date and end-date as normal but if a term cannot be found could it provide a start_date which is actually the end date of the previous term and an end_date  which is actually the start date of the next term.

    It doesn't matter if the SSRS subscription produced is blank.

    In an ideal world we would be able to only run the SSRS subscription on dates that are during the school term but the subscription schedule options are not that advanced.

    Many thanks

    Stuart

    • This reply was modified 1 week, 5 days ago by  swinfield.
  • I cannot see the image which you have embedded, can you try again?

    Regarding this

    The SSRS subscription is disabling itself if the report/query runs during a school holiday period between the dates of 2 terms and a term cannot be found and therefore errors.

    I have not used SSRS subscriptions for a few years. Can you tell me more about the error? Presumably the underlying query is returning no data, rather than erroring out, and therefore I do not understand why there are any errors in this process.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kaj wrote:

    However, the main reason I suggested DATETIME2(0) was that the milliseconds which are always stored with DATETIME (and shown when doing a select) could have been the reason for the OP to have the dates stored as varchar. God knows I'm plenty irritated by seeing those when i do a simple select on a number of DATETIME columns. Most of the time they're irrelevant and just obscuring the result and taking up place. In those cases and for that reason I prefer DATETIME2(0).

    If nothing else I hope we can agree that both DATETIME and DATETIME2(0) are better choices than VARCHAR or CHAR when working with date+time.

    We definitely agree that dates should not be stored as VARCHARs, but it seems the OP has no choice with this system. Including the data type as prefix in the column name (txt...) is another irritant, which makes me suspect that the system's developers have a grounding in MS Access!

    On the types of system I tend to work with (insurance, DW, BI), my date-related columns are almost always either DATE or DATETIME. I do not remember ever needing a date column which resolved only to seconds, though I can see the appeal. As always, the needs of the application should drive the datatypes used.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply