0

Extract Date from varchar

I am currently using SQL Server 2012 and looking to extract a date from a string of text in a column defined as varchar.

An example of this is as follows:

Mouse,Mickey T  Jul 9, 2020 9:24 am EDT:

The table that this column belongs to is a representation of notes entered or updated in an application. The column itself represents text from the note.

Upon entry of the note in the application (new note or update made to the note), our system auto-creates the first line of the note update to be in the format listed above. I would like to be able to extract the date from the text when it is in the above format.

Is this possible? Thank you in advance.

jeggrad2001
  • 1
  • 1
  • Does this answer your question? [Converting a string to a datetime in MS SQL Server](https://stackoverflow.com/questions/41146880/converting-a-string-to-a-datetime-in-ms-sql-server) – OldProgrammer Jul 20 '20 at 23:15
  • 3
    This is a poor database design. Your system should be properly populating fields for the name, date, and reason for the change in separate columns apart from the notes. This would allow you to search for any of those columns without jumping through hoops to do so. – Ken White Jul 20 '20 at 23:25
  • @KenWhite, I agree. Our system has a separate table that stores the entered date/time of the note (named NoteDateTime), but we don't have a table that stores the edits to the same note (i.e. NoteEditDateTime). Unfortunately, we are stuck with this unless our vendor changes this in a future upgrade. A report was requested around this data, and logically, the requestor would like to be able to run this by entered or edited date. – jeggrad2001 Jul 21 '20 at 14:10

1 Answers1

1

Is the provided string always in the same format? This code will work if there is nothing else after the string, and that it contains a double space between the Name and month.

DECLARE @DateString VARCHAR(50) = 'Mouse,Mickey T  Jul 9, 2020 9:24 am EDT:'
DECLARE @DateExtract VARCHAR(50) = SUBSTRING(@DateString, CHARINDEX('  ', @DateString) + 1, --Starting Point: Find the double space
                                             LEN(@DateString) - CHARINDEX('  ', @DateString) - 5) --End Point: How many characters to extract?  Total Length - Starting Point - ' EDT:'

--Extraction Checking Script
SELECT @DateString AS InputString,
       CHARINDEX('  ', @DateString) + 1 AS [StartPoint], --Starting Point: Find the double space
       LEN(@DateString) - CHARINDEX('  ', @DateString) - 5 AS [EndPoint], --End Point: How many characters to extract?  Total Length - Starting Point - 5 ( to cater for the timezone: ' EDT:')
       @DateExtract AS ExtractedValue

-- Output Values
SELECT CONVERT(DATE, @DateExtract), CONVERT(DATETIME, @DateExtract)

EDIT: This version assumes the last colon could be mid string and therefore just been added at the beginning of a note. Therefore updated the code to find where the second colon appears in the text string.

DECLARE @DateString VARCHAR(50) = 'Mouse,Mickey T  Jul 09, 2020 09:24 am EDT: dasdasda'
DECLARE @DateExtract VARCHAR(50) = SUBSTRING(@DateString,                                                                                    --
                                             CHARINDEX('  ', @DateString) + 1,                                                               --Starting Point: Find the double space
                                             CHARINDEX(':', @DateString, CHARINDEX(':', @DateString) + 1) - CHARINDEX('  ', @DateString) - 4 --End Point: How many characters to extract?  Find the character position of the second colon (:) minus the character position of the double space minus the 4 characters for ' EDT'
)

--Extraction Checking Script
SELECT @DateString AS InputString,
       CHARINDEX('  ', @DateString) + 1 AS [StartPoint],                                                              --Starting Point: Find the double space
       CHARINDEX(':', @DateString) AS [Colon],                                                                        --Find the end point of the name/date entry significed by the location of the colon.
       CHARINDEX(':', @DateString, CHARINDEX(':', @DateString) + 1) - CHARINDEX('  ', @DateString) - 4 AS [EndPoint], --End Point: How many characters to extract?  Find the character position of the second colon (:) minus the character position of the double space minus the 4 characters for ' EDT'
       @DateExtract AS ExtractedValue

-- Output Values
SELECT CONVERT(DATE, @DateExtract), CONVERT(DATETIME, @DateExtract)
MatthewM
  • 11
  • 2