purplesoup
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.
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.
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'?
ASKER
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.
ASKER
#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"
#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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
select * from tbl
where right(svcDate,7) > substring(convert(varchar(
and vcDate like '% [01][0-9]/[12][09][0-9][0-
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
You might want to addOpen in new window
to make sure only dates formatted as mm/yyyy are checked for.