Previous Topic Next Topic

MySQL and dates

Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Aug 5, 2019 at 11:23 AM
 Blocked Profile - Aug 12, 2019 at 04:30 PM
Oh Dear. Another day, another problem ...

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 :^)
Related:

2 responses

Answer 1 / 2
Blocked Profile
Aug 5, 2019 at 01:21 PM
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;

0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Aug 5, 2019 at 02:24 PM
Thanks ac3mark,

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.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Updated on Aug 5, 2019 at 02:36 PM
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!
0
Blocked Profile
Updated on Aug 5, 2019 at 04:29 PM
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.
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Aug 5, 2019 at 06:32 PM
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

I hope I'm not making a fool of myself.
0
Blocked Profile
Updated on Aug 6, 2019 at 04:35 PM
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.
0
Answer 2 / 2
Blocked Profile
Updated on Aug 6, 2019 at 04:36 PM
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
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Aug 11, 2019 at 05:38 PM
Sorry ac3mark - Been away over the last week so haven't been able to test your solution. Hopefully next weekend I will be free. I'll keep you posted.
0
Blocked Profile
Aug 12, 2019 at 04:30 PM
No Problems.
0

Similar discussions

Corel Draw: Error Reading file problem
clinton_fields2 -
clinton_fields2 -

2 responses