SQL Statement to extract date strings in the format MM/YYYY

I've got a varchar(50) column in which the data sometimes ends with dates in the format MM/YYYY.

Is there an efficient way to search for those rows with dates that are less than two months from now - in a dynamic SQL statement, so "now" is not hard-coded?

This needs to work for SQL Server 2008 and later.
purplesoupProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
select * from tbl  where vcDate > substring(convert(varchar(10), dateadd(m, -2, getdate()), 103), 4, 7)

Open in new window

You might want to add
and vcDate like '__/____'

Open in new window

to make sure only dates formatted as mm/yyyy are checked for.
purplesoupProgrammerAuthor Commented:
Is this just going to work were vcDate is just a date string, not ends in a date string? I can't see where the "ends with" logic is?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Sorry, you are expecting to see vcDate to be like 'Story started some time before 12/2014'?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

purplesoupProgrammerAuthor Commented:
yep - well, some string will exist before the date anyway, and it may not always be a date at the end - hence "sometimes ends" - but if a date is present it will always be at the end of the string in the format MM/YYYY.
Vitor MontalvãoMSSQL Senior EngineerCommented:
It will be good if you provide us some data sample so we can help you better.
purplesoupProgrammerAuthor Commented:
#1 "blah blah blah 01/2015"
#2 "something else 08/2015"
#3 "random string 07/2015"
#4 "some other string 01/2016"
#5 "some other string but without a date"
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, will assume then that if there's a date then will always be on the last 7 characters of the field.
WITH CTE_ValidDates 
AS (SELECT '01/'+RIGHT(ColumnName,7) NewDate
	FROM TableName
	WHERE ISDATE('01/'+RIGHT(ColumnName,7))=1)
SELECT *
FROM CTE_ValidDates
WHERE CAST(NewDate AS DATETIME) > GETDATE()-60

Open in new window

NOTE: I didn't test the code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There is no efficient way. No index can be used, as we have to apply string functions and LIKE to the column.
select * from tbl
where right(svcDate,7) > substring(convert(varchar(10), dateadd(m, -2, getdate()), 103), 4, 7)
and vcDate like '% [01][0-9]/[12][09][0-9][0-9]'

Open in new window

I've set the filter condition to be very restrictive, but it still includes a "date" like '00/2911' ;-).
Scott PletcherSenior DBACommented:
For the code below, the date string can appear anywhere in the string, as shown by the test data:

SELECT varchar_column, SUBSTRING(varchar_column, date_start_byte, 7) AS [mm/yyyy]
FROM ( --your_table_name_here
    --sample data just for testing
    SELECT 'blah blah blah 01/2015' AS varchar_column UNION ALL
    SELECT 'something else 08/2015 and more' UNION ALL
    SELECT 'random string 07/2015' UNION ALL
    SELECT 'some other string 01/2016 which continues after the date' UNION ALL
    SELECT 'some other string but without a date'
) AS test_data
CROSS APPLY (
    SELECT PATINDEX('%[012][0-9]/[12][0-9][0-9][0-9]%', varchar_column) AS date_start_byte
) AS assign_alias_names1
CROSS APPLY (
    SELECT SUBSTRING(varchar_column, date_start_byte, 2) AS month,
        SUBSTRING(varchar_column, date_start_byte + 3, 4) AS year
) AS assign_alias_names2
WHERE
    date_start_byte > 0 AND
    month BETWEEN '01' AND '12' AND
    year + month < CONVERT(varchar(6), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0), 112)
purplesoupProgrammerAuthor Commented:
Just to say for some reason this:
select * from tbl
where right(svcDate,7) > substring(convert(varchar(10), dateadd(m, -2, getdate()), 103), 4, 7)
and vcDate like '% [01][0-9]/[12][09][0-9][0-9]'

does not seem to work quite right - if you do this:

SELECT 1 WHERE ('01/2016' <      '05/2015')

it returns 1, and obviously the idea is it shouldn't.
Scott PletcherSenior DBACommented:
FYI, be aware that Vitor's code requires that dd/mm/yyyy be recognized by default as a date, i.e., it requires a dateformat of ddmmyyyy.  That should be OK for the UK, so again, it's just an FYI.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That is because I'm too stupid to think properly, obviously.
select * from tbl
where right(vcDate,4)+substring(right(vcDate,7),2) > left(convert(varchar(10), dateadd(m, -2, getdate()), 112), , 6)
and vcDate like '% [01][0-9]/[12][09][0-9][0-9]'

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.