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:
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- 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.
-
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).
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
-
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
- 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply