none
convert string to date RRS feed

  • Question

  • Hi 

    I have a filenames as    abcd_efgh_stry_2018-11-02.xml     ghjk_tfgh_2018-11-02.xml , I need an help on how to extract date from this strings.

    Please advice

    Thanks

    Monday, July 22, 2019 9:47 PM

Answers

  • Hi Rajm0019,

    Here we go:

    DECLARE @tbl TABLE (
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, [fileName] VARCHAR(100)
    );
    INSERT INTO @tbl([fileName])
    VALUES
    	('abcd_efgh_stry_2018-11-02.xml')
    	, ('ghjk_tfgh_2018-11-02.xml');
    
    SELECT [fileName] 
    	, CAST(LEFT(RIGHT([fileName], 14), 10) AS DATE) AS [fileDate]
    FROM @tbl;

    Output:

    ID	fileName	            fileDate
    1	abcd_efgh_stry_2018-11-02.xml	2018-11-02
    2	ghjk_tfgh_2018-11-02.xml	2018-11-02
    Monday, July 22, 2019 10:05 PM
  • Hi Rajm,

    If the format of final string  'xxxx-xx-xx.xml' is fixed, then you can use following code: 

    IF OBJECT_ID('test') IS NOT NULL drop table test
    create table test(
    col varchar(100))
    go
    insert into test values
    ('abcd_efgh_stry_2018-11-02.xml'),
    ('ghjk_tfgh_2018-11-02.xml')
    
    select reverse(substring(reverse(col),5,10))
    from test
    
    /*
    2018-11-02
    2018-11-02
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, July 23, 2019 2:08 AM
  • create table #temp (col1 varchar(200))
    insert #temp values ('abcd_efgh_stry_2018-11-02.xml'),('ghjk_tfgh_2018-11-02.xml')

    select *,convert(date,substring(col1,patindex('%[0-9]%',col1),patindex('%.xml%',col1)-patindex('%[0-9]%',col1)))
    from #temp

    Mark as answer if it helps. Thanks.
    • Edited by Soumen Barua Tuesday, July 23, 2019 3:03 AM
    • Marked as answer by Rajm0019 Tuesday, July 23, 2019 2:31 PM
    Tuesday, July 23, 2019 3:03 AM
  • HI

    create table #a
    (
    ID INT IDENTITY(1,1) PRIMARY KEY
    , [fileName] VARCHAR(100)
    )

    INSERT INTO #a([fileName])
    VALUES
    ('abcd_efgh_stry_2018-11-02.xml')
    , ('ghjk_tfgh_2018-11-02.xml');

    --select * from #a;

    SELECT *,CAST(REVERSE(SUBSTRING(REVERSE(FILENAME),5,CHARINDEX('_',REVERSE(FILENAME),1)-5)) AS DATE)DD FROM #A

    Thanks

    • Proposed as answer by Abhiramdw Tuesday, July 23, 2019 12:41 PM
    • Marked as answer by Rajm0019 Tuesday, July 23, 2019 2:32 PM
    Tuesday, July 23, 2019 7:16 AM

All replies

  • Hi Rajm0019,

    Here we go:

    DECLARE @tbl TABLE (
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, [fileName] VARCHAR(100)
    );
    INSERT INTO @tbl([fileName])
    VALUES
    	('abcd_efgh_stry_2018-11-02.xml')
    	, ('ghjk_tfgh_2018-11-02.xml');
    
    SELECT [fileName] 
    	, CAST(LEFT(RIGHT([fileName], 14), 10) AS DATE) AS [fileDate]
    FROM @tbl;

    Output:

    ID	fileName	            fileDate
    1	abcd_efgh_stry_2018-11-02.xml	2018-11-02
    2	ghjk_tfgh_2018-11-02.xml	2018-11-02
    Monday, July 22, 2019 10:05 PM
  • Hi Rajm,

    If the format of final string  'xxxx-xx-xx.xml' is fixed, then you can use following code: 

    IF OBJECT_ID('test') IS NOT NULL drop table test
    create table test(
    col varchar(100))
    go
    insert into test values
    ('abcd_efgh_stry_2018-11-02.xml'),
    ('ghjk_tfgh_2018-11-02.xml')
    
    select reverse(substring(reverse(col),5,10))
    from test
    
    /*
    2018-11-02
    2018-11-02
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, July 23, 2019 2:08 AM
  • create table #temp (col1 varchar(200))
    insert #temp values ('abcd_efgh_stry_2018-11-02.xml'),('ghjk_tfgh_2018-11-02.xml')

    select *,convert(date,substring(col1,patindex('%[0-9]%',col1),patindex('%.xml%',col1)-patindex('%[0-9]%',col1)))
    from #temp

    Mark as answer if it helps. Thanks.
    • Edited by Soumen Barua Tuesday, July 23, 2019 3:03 AM
    • Marked as answer by Rajm0019 Tuesday, July 23, 2019 2:31 PM
    Tuesday, July 23, 2019 3:03 AM
  • HI

    create table #a
    (
    ID INT IDENTITY(1,1) PRIMARY KEY
    , [fileName] VARCHAR(100)
    )

    INSERT INTO #a([fileName])
    VALUES
    ('abcd_efgh_stry_2018-11-02.xml')
    , ('ghjk_tfgh_2018-11-02.xml');

    --select * from #a;

    SELECT *,CAST(REVERSE(SUBSTRING(REVERSE(FILENAME),5,CHARINDEX('_',REVERSE(FILENAME),1)-5)) AS DATE)DD FROM #A

    Thanks

    • Proposed as answer by Abhiramdw Tuesday, July 23, 2019 12:41 PM
    • Marked as answer by Rajm0019 Tuesday, July 23, 2019 2:32 PM
    Tuesday, July 23, 2019 7:16 AM
  • DECLARE @tbl TABLE (
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, [fileName] VARCHAR(100)
    );
    INSERT INTO @tbl([fileName])
    VALUES
    	('abcd_efgh_stry_2018-11-02.xml')
    	, ('ghjk_tfgh_2018-11-02.xml');
    
    select substring(fileName, charindex('-', fileName)-4, 10) from @tbl
    Tuesday, July 23, 2019 11:48 AM
  • SELECT *,Reverse(Replace(Stuff(REVERSE(FILENAME),CHARINDEX('_',REVERSE(FILENAME)),Len(FILENAME),'') ,'lmx.','')) dt 
    FROM  test

    Tuesday, July 23, 2019 2:23 PM
    Moderator
  • Hi Jingyang 

    what does lmx stands for in your query 

    Thanks

    Raj

    Tuesday, July 23, 2019 2:34 PM
  • The reverse of .xml is lmx.. I just want to remove it from the final string segment with replace function.
    Tuesday, July 23, 2019 2:50 PM
    Moderator
  • If you have only three underscore in your xml filename, you can try this as well:

    SELECT replace(parsename(replace(replace([fileName],'.','|'),'_','.'),1),'|xml','')
     FROM  test

    Tuesday, July 23, 2019 3:40 PM
    Moderator