I have a diary database that has a column in it that has at least one date mixed in with some other text. The date is in a string form of dd/mm/yyyy format so the entry against the data entry has something to the effect of "on 02/05/2019 John did this. On 17/05/2019 Simon said that".
What I need is a way of extracting the dates and comparing it against a specified date range that I input on a query page so I can find out a history of what records have this date in it.
So for this example I want to input a date range of 28/04/2019 to 07/05/2019, and I want to be able to pull out this record (and any other record that has a date in it within the designated criteria).
Is this possible? I did think of adding another column to add the important dates as a list and comparing against that, but the same problem arises ... How to select a date from a string. Any ideas would be appreciated.
At this stage just a generic pointer would be good - lets see if I have learned enough to figure out how to use the commands you suggest :^)
I see how that would work for 1 date (I think), but how do I set the @searchstring to be any date between the 2 dates I am interested in - like in the example in the original question between 28/04/2019 and 07/05/2019 so that it would include all of the dates within that range?
Perhaps I am missing something - I haven't tried this yet - I'm being a naughty boy and not testing this first before I jump in with a response. Would I use something like
WHERE Description LIKE @searchstring BETWEEN '%28/04/2019%' AND '%07/05/2019%'
You are correct - the main text is in a VARCHAR column.
I have the possibility of adding a column to the database that just lists the important dates. No additional text - perhaps a deliminator so the list could be something like ...
[date 1] - [date 2] - [date 3] - [date 4] - etc
If this is a good option what sort of column should it be set to? VARCHAR? I guess it cant be a DATE field as 2 dates or more would make the database think the input was of the wrong type?
Would it be easier to check if a date between the 2 dates I am searching is in that list of dates?
I know this is very vague, but I'm not quite sure how to make this clear at all!
If you can add in the date, that would be the ideal fix, as then it can be a range. Right now, the string (varchar) cannot be placed into a range, but you knew that. Let me look up the syntax to one more command, and I will post a possible fix.
I was looking into PATINDEX. It returns the index number of the pattern when found.
Here is an example:
select
cast(substring('On 02/05/2019 John did this.',
patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',
''On 02/05/2019 John did this.'), 10) as datetime)
So, if this returns the proper date, we then would have a subset of data to query from, and we can apply a range to it... So, where the TEXT description is, we need to extend this to search each DESCRIPTION.
Do you see what we are building? This is a query to build the descriptions into a date, then we can query the dates as a range, and get the original ID of the description.
Sorry ac3mark,
I'm only 1/2 getting what you are trying to get at. I can see that you are making a date out of the main data field, but in that field these can be many dates. I'm wondering if I haven't explained this very well as I'm not sure you are getting that the one field has many dates in it. Let me try to give you an example of the table I have (or could have if we added the extra column.
ID | Main data as a VARCHAR ............................... | Possible extra column ============================================================================== 01 | 02/05/2019 John did this. On 17/05/2019 Simon said ... | 02/05/2019 17/05/2019 ----------------------------------------------------------------------------------- 02 | 03/04/2019 this happened ............................. | 03/04/2019 ------------------------------------------------------------------------------------ 03 | 27/12/2018 we met. 01/02/2019 I fell. 02/04/2019 I ate | 27/12/2018 01/02/2019 02/02/2019 ------------------------------------------------------------------------------------
(the constraints of the code make the dates in the last entry go to the next line).
So what I want is that if I enter a search date range of 01/01/2019 to 05/04/2019 then it will bring in 2 unique results - ID 02 and ID 03
You are making sense. I am just showing you how we can extract the date from the string, to convert it into a date to be evaluated by a range. I will set up a table to reconstruct your data set to test a query. Stick around.
select reciD, description, convert(date, substring(Description,patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',Description),10) ,103) as datetime INTO #RESULTS_DATES from [dbo].[Incident] where patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',Description) > 0
SELECT * FROM #RESULTS_DATES WHERE DATETIME > '2019-04-04'
- Posts
- 1005
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- October 2, 2019
208 -Nice to hear from you again.
I see how that would work for 1 date (I think), but how do I set the @searchstring to be any date between the 2 dates I am interested in - like in the example in the original question between 28/04/2019 and 07/05/2019 so that it would include all of the dates within that range?
Perhaps I am missing something - I haven't tried this yet - I'm being a naughty boy and not testing this first before I jump in with a response. Would I use something like
WHERE Description LIKE @searchstring BETWEEN '%28/04/2019%' AND '%07/05/2019%'
You are correct - the main text is in a VARCHAR column.
- Posts
- 1005
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- October 2, 2019
208 -[date 1] - [date 2] - [date 3] - [date 4] - etc
If this is a good option what sort of column should it be set to? VARCHAR? I guess it cant be a DATE field as 2 dates or more would make the database think the input was of the wrong type?
Would it be easier to check if a date between the 2 dates I am searching is in that list of dates?
I know this is very vague, but I'm not quite sure how to make this clear at all!
- Posts
- 13031
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- October 11, 2019
1571 -I was looking into PATINDEX. It returns the index number of the pattern when found.
Here is an example:
select
cast(substring('On 02/05/2019 John did this.',
patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',
''On 02/05/2019 John did this.'), 10) as datetime)
So, if this returns the proper date, we then would have a subset of data to query from, and we can apply a range to it... So, where the TEXT description is, we need to extend this to search each DESCRIPTION.
Do you see what we are building? This is a query to build the descriptions into a date, then we can query the dates as a range, and get the original ID of the description.
- Posts
- 1005
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- October 2, 2019
208 -I'm only 1/2 getting what you are trying to get at. I can see that you are making a date out of the main data field, but in that field these can be many dates. I'm wondering if I haven't explained this very well as I'm not sure you are getting that the one field has many dates in it. Let me try to give you an example of the table I have (or could have if we added the extra column.
(the constraints of the code make the dates in the last entry go to the next line).
So what I want is that if I enter a search date range of 01/01/2019 to 05/04/2019 then it will bring in 2 unique results - ID 02 and ID 03
I hope I'm not making a fool of myself.
- Posts
- 13031
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- October 11, 2019
1571 -