SQL Server – How to Extract Only Date from String

sql server

I have one field in database and I am getting the multiple dates in the same field. I want to extract multiple dates from the string. Please help me on this
String like:

"7/3/2019 1:20 AM – Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM – Request assigned to 7/3/2019 3:22 AM – PO123456 created"

Best Answer

With the string you have in your example, you can use STRING_SPLIT as the first step

DECLARE @tags NVARCHAR(400) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created'  

SELECT value  
FROM STRING_SPLIT(@tags, '-')  
WHERE RTRIM(value) <> '';

Which gives results

value
7/3/2019 1:20 AM 
Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM 
Request assigned to 7/3/2019 3:22 AM 
PO123456 created

You could then use RIGHT and or TRIM to leave only the dates

Select RIGHT ( ' Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM ' , 18 )

Gives

7/3/2019 1:34 AM

If you want them to be dates at the end, you would need to use CAST / CONVERT to change the string to date format.

Related Solutions

sql-server,xml – How to Extract XML Attribute from a Column

The XQuery expression in the value() function must return at most one value.

Your expression EventFileTarget/file/@name[1] is saying that from all EventFileTargetnodes you want all file nodes and from those nodes you want the first name attribute.

Given this sample XML:

<EventFileTarget>
  <file name = "FileName 1"/>
  <file name = "FileName 2"/>
</EventFileTarget>
<EventFileTarget>
  <file name = "FileName 3"/>
  <file name = "FileName 4"/>
</EventFileTarget>

Your expression returns all four filenames.

Change the XQuery to (EventFileTarget/file/@name)[1] to get the first name attribute with the path EventFileTarget/file.

With XML that looks like this

<EventFileTarget>
  <file/>
  <file/>
</EventFileTarget>
<EventFileTarget>
  <file/>
  <file name = "FileName 4"/>
</EventFileTarget>

The result (EventFileTarget/file/@name)[1] is FileName 4.

In your case there can probably only be one EventFileTarget node and only one file node but SQL Server does not know that so you have to build the XQuery to guarantee only one value returned. (EventFileTarget/file/@name)[1].

SQL Server – How to Extract Time from Date into New Column

Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0)

MSDN: TIMEFROMPARTS (Transact-SQL)

With SQL Server prior to 2012, you can use such queries:

Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)

Best Answer

Related Solutions

sql-server,xml – How to Extract XML Attribute from a Column

SQL Server – How to Extract Time from Date into New Column

Related Question