Link to home
Start Free TrialLog in
Avatar of purplesoup
purplesoupFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of Qlemo
Qlemo
Flag of Germany image

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.
Avatar of purplesoup

ASKER

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?
Sorry, you are expecting to see vcDate to be like 'Story started some time before 12/2014'?
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.
It will be good if you provide us some data sample so we can help you better.
#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"
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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