Home Forums SQL Server 2012 SQL Server 2012 - T-SQL extract date RE: extract date

  • February 26, 2013 at 1:41 pm

    #1591310

    monilps (2/26/2013)

    Hey,

    Thank you for quick response

    What I meant was

    For Ex:

    Row1: aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds

    Row2: asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn

    Row3: sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn

    Row4: fdklnsdlnf 02-02 fjsksngjk

    Row5: sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk

    So I need result set as

    Row1: 02/20/2002

    Row2: 9/3/99

    Row3: 02-02-02

    Row4: 02-02

    Row5: 02/02

    Thanks,

    the technique i posted will work for all rows, and even better, will also catch when a comment has multiple dates in the same string.

    get the function, modify my query to use your table and data, and let us know if it's doing what you expected.

    I made a best guess for your data, so i can provide an example, but if you want a working query, it's up to you to provide the CREATE TABLE definition and sample data for testing

    With mySampledata(id,SomeComment)

    AS

    (SELECT 1,'- aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds' UNION ALL

    SELECT 2,'- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn' UNION ALL

    SELECT 3,'- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn' UNION ALL

    SELECT 4,'- fdklnsdlnf 02-02 fjsksngjk' UNION ALL

    SELECT 5,'- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk')

    SELECT mySampledata.*,

    myfn.* ,

    Case when IsDate(myfn.Item)=1 THEN CONVERT(datetime,myfn.Item) ELSE NULL END AS PotentialDate

    FROM mySampledata

    CROSS APPLY dbo.DelimitedSplit8K(SomeComment,' ') Myfn

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!