How to take a value hhmmss from the file

Question

How to take a value hhmmss from the file

Guvera

SSC-Addicted

Points: 426
More actions
May 12, 2021 at 3:44 am
Go to Answer
#3882160

Hello,
I have a file in this format "Vendor_ABC_Pro_20210422T205535". In this file, we have this datetime 20210422T205535. I need 205535 (hhmmss) from this format 20210422T205535 after T. Can you please help on this?
Thanks.

Adi Cohn

SSC-Addicted

Points: 424
More actions
May 12, 2021 at 7:49 am
Answer
#3882221
I think that searching just for the first occurrence of the letter T is not good, because you can also have a file that has the letter T in it's name. Because of that I think that it is better to use the function patindex to look for the first occurrence of the pattern of 8 numbers then T then 6 numbers.
```
DECLARE @FileString VARCHAR(100) = 'Vendotr_ABC_12T34_Pro_20210422T205535';
DECLARE @Pos INT = patindex('%[0-3[0-9][0-9][0-9][0-9][0-9][0-9][0-9]T[0-9][0-9][0-9][0-9][0-9][0-9]%',@FileString) + 8

select SUBSTRING(@FileString, @Pos,6), @Pos
```
Adi

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

I think this works... <g> Oh right... the DATEADD stuff is just to confirm that it's actually a date at that point. It is. <g>

use tempdb;
go

DECLARE @FileString VARCHAR(30) = 'Vendor_ABC_Pro_20210422T205535';
DECLARE @DateString CHAR(12);
DECLARE @Yr CHAR(4), @Mo CHAR(2), @Day CHAR(2);

-- return the date 20210422 (8 characters to the left of the letter T)
DECLARE @Pos INT = CHARINDEX('T',@FileString,1)

SET @DateString = SUBSTRING(@FileString,@Pos-8,8);
SET @Yr = LEFT(@DateString,4);
SET @Mo = SUBSTRING(@DateString,5,2);
SET @Day = SUBSTRING(@DateString,7,2);

PRINT 'Date: ' + @DateString;
PRINT 'Year: ' + @Yr;
PRINT 'Month: ' + @Mo;
PRINT 'Day: ' + @Day;

PRINT DATEFROMPARTS(@Yr, @Mo, @Day);
PRINT DATEADD(day,1,DATEFROMPARTS(@Yr,@Mo,@Day));

This reply was modified 3 years, 6 months ago by pietlinden.
This reply was modified 3 years, 6 months ago by pietlinden. Reason: me mordio la estupidez!

Guvera SSC-Addicted Points: 426 More actions · Answer 2

Thanks for your reply.

Sorry, One small correction. File Name is " Vendor_ABC_Pro_20210422T205535_Region_20210422_20210422.json".

In this file, we have this datetime 20210422T205535. I need 205535 (hhmmss) from this format 20210422T205535 after T. Can you please help on this?

Appreciate your help!

Thanks.

Guvera SSC-Addicted Points: 426 More actions · Answer 3

Thanks Adi Cohn,

You are given solution that is really helpful to me.

Thanks