CODEWITHSUNDEEP
sqlstringt-sql
sparklingstar
sparklingstar

Reputation: 21

SQL to extract a part of string

I am trying to extract timestamp out of a filename. The filename looks like 'Relabels for 10-19-2019 15-16-24'. I want to extract the timestamp i.e., 15-16-24 out of that filename. Can anyone help me with the SQL query? I tried using substring but substring doesn't work for all filenames.

Upvotes: 0

Views: 68

Answers (2)

tmaj
tmaj

Reputation: 34967

You could be very explicit and use DATETIMEFROMPARTS and SUBSTRING.

declare @dt_string varchar(100) = right('Relabels for 10-19-2019 15-16-24', (8+2)+1+(6+2));

select DATETIMEFROMPARTS(
  SUBSTRING(@dt_string,7,4), -- year
  SUBSTRING(@dt_string,1,2), -- month
  SUBSTRING(@dt_string,4,2), -- day
  SUBSTRING(@dt_string,12,2), -- hour
  SUBSTRING(@dt_string,15,2), -- minute
  SUBSTRING(@dt_string,18,2), -- second
  0 -- millisecond
  ) as dt_parsed;

dt_parsed
2019-10-19 15:16:24.000

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If 15-16-24 is intended to be 15:16:24 then I understand it as a time. You can extract it by using right():

select right(filename, 8)

You can even convert it to a time:

select try_convert(time, replace(right(filename, 8), '-', ':'))

Upvotes: 2

Related Questions