How to take a value hhmmss from the file

  • 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.

     

  • 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 1 month ago by  pietlinden.
    • This reply was modified 1 month ago by  pietlinden. Reason: me mordio la estupidez!
  • 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.

  • 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

  • Thanks Adi Cohn,

    You are given solution that is really helpful to me.

    Thanks

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

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