2 replies
Blocked Profile
If the entry is a varchar, then just use like, as in
Declare @searchstring varchar (20)
Set @searchstring ='% 02/05/2019%'
Select id
From
Incident
Where
Description like @searchstring;
Declare @searchstring varchar (20)
Set @searchstring ='% 02/05/2019%'
Select id
From
Incident
Where
Description like @searchstring;
Blocked Profile
Give this a try:
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'
DROP TABLE #RESULTS_DATES
- Posts
-
1010
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- June 16, 2020
247Nice 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
-
1010
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- June 16, 2020
247[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!
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
-
1010
- Registration date
- Saturday January 17, 2015
- Status
- Moderator
- Last seen
- June 16, 2020
247I'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.