extract date

Question

extract date

monilps

SSCommitted

Points: 1686
More actions
February 26, 2013 at 12:44 pm

#290277

I need extract date from one column which has datatype text.
For Ex:
- aaaaaaa 02/20/2002 jjjjjj sddsdjjjjsdsd sdsds
- asdlajsd fjsdfjkdgn dsjbfks, dfjksflml,fsf,f sdfsdf 9/3/99 sjdfnsdnf sjdfoisofn
- sdkfjos fjsdopfj uyro dlsfl 02-02-02 sflnsldfn sdkflsn
- fdklnsdlnf 02-02 fjsksngjk
- sdkflsdnfl 02/02 hisfdhiif sdjfsdkfk

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it'll take a bit of work, and you'll need the DelimitedSplit8K function (search SSC and read the article)

things like 02/02 and 02-02 are not dates, so they do not get caught in my example...you'll have to add additional pattern matching on the myfn.Item like i've done with the ISDATE function...something like 'LIKE [0-9][0-9]/[0-9][0-9]

and other similar patterns.

With mySampledata(id,SomeComment)

AS

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

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

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

- fdklnsdlnf 02-02 fjsksngjk

- 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!

monilps SSCommitted Points: 1686 More actions · Answer 2

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,

Lowell SSC Guru Points: 323518 More actions · Answer 3

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')