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.
-
215-16-24 is a time stamp???Gordon Linoff– Gordon Linoff2019-10-28 21:35:12 +00:00Commented Oct 28, 2019 at 21:35
-
1What filenames did you have a problem with?PM 77-1– PM 77-12019-10-28 21:39:26 +00:00Commented Oct 28, 2019 at 21:39
-
"I tried something with other data and it didn't work, but I won't share the code, the data, or how it failed" doesn't help us help you.HABO– HABO2019-10-28 21:53:49 +00:00Commented Oct 28, 2019 at 21:53
Add a comment
|
2 Answers
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), '-', ':'))
Comments
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
Comments
