Extracting date from text field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Extracting date from text field

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-29 : 11:07:53

I have a table

transactions
------------

Notes (Text)

Some rows contain the string 'Sale date - xx/xx/xxxx'.

I want to return all rows where the date after the 'Sale date = ' portion of the string plus 10 years is less than today's date.

How can I do this please?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-10-29 : 11:59:41

try select * from Transaction where where notes like '%/%/%'
this will not return the rows which has date but it will close...

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

parody
Posting Yak Master

111 Posts

Posted - 2009-10-29 : 12:17:42

convert it to a smalldatetime first i.e. something like (i am in uk)

SET dateformat dmy
select ...
from transactions
where dateadd(yy,10,convert(smalldatetime,right(rtrim(Notes),10)) < getdate()

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-10-29 : 12:32:28

[code]DECLARE @Table TABLE(transactions varchar(100))
INSERT INTO @Table SELECT
'aaaaaaaaaSale date - 12/12/2001' UNION SELECT
'aaaaaaaa Sale date - 12/12/1999' UNION SELECT
'Sale date - 12/12/1989aaaaaaaa' UNION SELECT
'Sale date - 12/12/1987 aaaaaaa' UNION SELECT
'Sale date - 12/12/1990'

SELECT *
FROM @Table
WHERE DATEADD(YEAR, 10, SUBSTRING(transactions, CHARINDEX('Sale date - ', transactions)+12, 10)) < GETDATE()[/code]

Hope can help...but advise to wait pros with confirmation...

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-29 : 13:14:45

Many thanks - much appreciated.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 04:09:38

Hw would I handle the situation where a row did not contain the string 'Sale date - ' please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 04:25:39

DECLARE @Table TABLE(transactions varchar(100))
INSERT INTO @Table SELECT
'aaaaaaaaaSale date - 12/12/2001' UNION SELECT
'aaaaaaaa Sale date - 12/12/1999' UNION SELECT
'Sale date - 12/12/1989aaaaaaaa' UNION SELECT
'Sale date - 12/12/1987 aaaaaaa' UNION SELECT
'Sale date - '

SELECT *
FROM @Table
WHERE DATEADD(YEAR, 10, SUBSTRING(transactions, CHARINDEX('Sale date - ', transactions)+12, 10)) < GETDATE()
AND LEN(transactions)>12

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 04:56:48

Many thanks for your reply.

However, (sorry, I should have been clearer in my initial question) the substring 'Sale date - ' can actually be anywhere within the text field so the > 12 is of no use.

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 05:17:24

use a patindex then and base it on the date not the presence of the text

to use the previous example:

DECLARE @Table TABLE(transactions varchar(100))
INSERT INTO @Table SELECT
'aaaaaaaaaSale date - 12/12/2001' UNION SELECT
'aaaaaaaa Sale date - 12/12/1999' UNION SELECT
'Sale date - 12/12/1989aaaaaaaa' UNION SELECT
'Sale date - 12/12/1987 aaaaaaa' UNION SELECT
'Sale date - 12/12/1990'

SELECT *
FROM @Table
WHERE DATEADD(YEAR,10,substring(transactions,patindex('%/%/%',transactions)-2,10)) < GETDATE()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources