none
extract date from text string - Transact-SQL RRS feed

  • Question

  • Hello,

    I have a field in my database with an archived date... (Giampaoli  Live Oak, Almonds Archive 09/16/10)

    I need to be able to extract the date from this, then perform a datepart function on it... How do I extract the date into it's own value using just SQL?

    Thanks,

    Wes

    Friday, September 17, 2010 3:25 PM

Answers


  • select 
    ID,
    NAME,
    datepart(month, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS MONTHARCHIVED,
    datepart(YEAR, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS YEAR_ARCHIVED
    FROM
    TABLEABC
    
    
    • Marked as answer by Wes Crockett Wednesday, September 22, 2010 5:55 PM
    Friday, September 17, 2010 6:44 PM

All replies

  • Hello,

    I have a field in my database with an archived date... (Giampaoli  Live Oak, Almonds Archive 09/16/10)

    I need to be able to extract the date from this, then perform a datepart function on it... How do I extract the date into it's own value using just SQL?

    Thanks,

    Wes


    Give some more examples.

     

    Will date always be at last part?

    will there be any numbers before the date in string?

    Friday, September 17, 2010 3:27 PM
  • Here is one way

    DECLARE @t VARCHAR(MAX) = 'Giampaoli Live Oak, Almonds Archive 09/16/10'
    
    SELECT SUBSTRING(@t,PATINDEX('%__/__/__%',@t),50)
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    Friday, September 17, 2010 3:28 PM
  • You need to use a pattern matching (with Patindex). T-SQL is not a good language for such problems, it may be better done with RegEx on the client. There is also CLR RegEx implementation - the link was given here few times - try a search.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, September 17, 2010 3:29 PM
    Moderator
  • declare @st varchar(max)
    set @st = 'Giampaoli Live Oak, Almonds Archive 09/16/10'
    
    select SUBSTRING(@st, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', @st), 50)

    • Proposed as answer by ryguy72 Thursday, April 17, 2014 8:25 PM
    Friday, September 17, 2010 3:31 PM
  • This is great!

    Is there a way to do it recursively as part of a query? For example, if i did:

    Select ID, Name from TABLE
    

    I would get about 1200 results... about 140 of them have this issue where I need to extract the date and then run a datepart on it as well

    My goal would be a query that had the following:

    ID, Name, datepart(month, ARCHIVE_DATE) as 'Month Archived, datepart(year, ARCHIVEDATE) as 'Year Archived'

    Thank you,

    Wes

    Friday, September 17, 2010 6:38 PM

  • select 
    ID,
    NAME,
    datepart(month, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS MONTHARCHIVED,
    datepart(YEAR, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS YEAR_ARCHIVED
    FROM
    TABLEABC
    
    
    • Marked as answer by Wes Crockett Wednesday, September 22, 2010 5:55 PM
    Friday, September 17, 2010 6:44 PM
  • Thanks Guys,

    This helped me:

    select 
    ID,
    NAME,
    datepart(month, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS MONTHARCHIVED,
    datepart(YEAR, CONVERT(date, SUBSTRING(YOUR_COLUMN_NAME, patindex('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%', YOUR_COLUMN_NAME), 50))) AS YEAR_ARCHIVED
    FROM
    TABLEABC


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, April 16, 2014 6:43 AM