Seleting text that has dates - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Seleting text that has dates

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-10-06 : 00:11:53

Hi,

I have a table that has filenames and some of the file name contains dates in it. Some rows has dates in YYYYMMDD format as part of text, some rows has YYYMM date format as part of their text and some row does not have dates in them at all.

e.g:
Table A:

FileName
-----------------
ABCD.20051201.dat
XYZ.dcntry.20060412.mdss
roger.dat
mnbc.200402.cimms
pro.doc
Emm.pvtplcy

I am trying to write two different query against this table:

1) Grab only those records that contains dates in it.Grab only the rows that has dates in them.

ABCD.20051201.dat
XYZ.dcntry.20060412.mdss
mnbc.200402.cimms

2) Parse the text and grab the dates from the text and subtract that dates the current date.
For example:
First row above has date 20051201. Subtract it with current date 20061005. Like 20060605 - 20051201

Also, for the text that contains only YYYYMM format date like 200402 needs to be subtract with current year and month like:
200610 - 200402.

Will appreciate any help and advice in this regard.

Thanks
Raj

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-06 : 01:01:09

select 
substring(substring(x,patindex('%[0-9]%', x),len(x)),1,charindex('.',substring(x,patindex('%[0-9]%', x),len(x)))-1) as FileDate
Into #temp1
from
(select 'ABCD.20051201.dat' as x union all
select 'XYZ.dcntry.20060412.mdss' as x union all
select 'roger.dat' as x union all
select 'mnbc.200402.cimms' as x
) t
where isdate(substring(substring(x,patindex('%[0-9]%', x),len(x)),1,charindex('.',substring(x,patindex('%[0-9]%', x),len(x)))-1)) = 1

select 
	FileDate,
	(case len(FileDate)
		when 8 then datediff(d, convert(datetime, FileDate), getdate()) + 1
		when 6 then datediff(d, convert(datetime, FileDate + '01'), getdate())+1
	end) as Diff
from #temp1

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-06 : 10:24:50

Hereafter, Always use proper DATETIME datatype to store Dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources