Extract Date Time from String as datetime format

Question

Post reply

Extract Date Time from String as datetime format

SQL Server

Old Hand

Points: 377
More actions
March 24, 2020 at 1:14 pm
Go to Answer
#3736578
We pass this file name as parameter in stored procedure and would like to get the date and time from this file name. The Date and Time is between ViewId_ and .xls
I use the below query, but I am stuck on how to remove the h, min, s from the time. Basically I want date and time to be stored in datetime variable.
```
DECLARE @Text nvarchar(250) = 'CTranTyp-ViewId_2020.03.24_13h05min10s.xls'

select Replace(Reverse(stuff(reverse(@Text),CHARINDEX(reverse('ViewId_'),reverse(@Text)),len(@Text),'')),'.xls','')
```
Desired Output:
2020-03-24 13:05:10

Thanks!

Phil Parkin

SSC Guru

Points: 246964

March 24, 2020 at 1:48 pm

Answer

#3736602

I'm sure that this could be simplified with a bit more thought, but this works for the example provided:

DECLARE @Text NVARCHAR(250) = N'CTranTyp-ViewId_2020.03.24_13h05min10s.xls';

SELECT
       CAST(REPLACE(
                       REPLACE(
                                  REPLACE(
                                             REPLACE(
                                                        SUBSTRING(@Text, calcs.StartPos, calcs.EndPos - calcs.StartPos)
                                                       ,'.'
                                                       ,'-'
                                                    )
                                            ,'_'
                                            ,' '
                                         )
                                 ,'h'
                                 ,':'
                              )
                      ,'min'
                      ,':'
                   ) AS DATETIME)
FROM
(
    SELECT StartPos = CHARINDEX('_', @Text) + 1
          ,EndPos = CHARINDEX('s.xls', @Text)
) calcs;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

I think what you really want is DATETIMEFROMPARTS()

https://docs.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver15

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Phil Parkin SSC Guru Points: 246964 More actions · Answer 2

jonathan.crawford wrote:

I think what you really want is DATETIMEFROMPARTS()
https://docs.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver15

I started out down this path, but found that carving out all of the individual components resulted in an even longer piece of code than what I posted!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Using an idea similar to Phil's good code, if the date and time notation and the extension will always be the same, you could do this just for a little simplicity.

DECLARE @Text nvarchar(250) = 'CTranTyp-ViewId_2020.03.24_13h05min10s.xls';
 SELECT D1 = CONVERT(DATETIME,REPLACE(REPLACE(REPLACE(LEFT(RIGHT(@Text COLLATE Latin1_General_BIN,26),21),'_',' '),'h',':'),'min',':'));

As for converting it to a specific format goes, why bother? I'd use it as a date/time in almost every case. Why do you need it in a specific format (which is also easy to do) rather than a date/time?

Also, I added the COLLATE clause to speed up the nested REPLACEs just in case you have a wad of these to do in a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)