Reputation: 45
Below is my query, I am looking to pull records where only the substring value (which will be YYYY) is less than current year - 25 years.... and I should mention that this field is varchar and probably needs to be converted which I haven't been able to do successfully either.
SELECT
AccountNumber,
LoanPrimeLongName,
convert (varchar,LoanOpenDate,103)LoanOpenDate,
LoanOriginalBalance,
LoanBalance,
LoanInterestRate,
LoanRemainingTermMonths,
LoanDelqDays,
LoanDescription
FROM
ARCU.ARCULoanDetailed
WHERE
(((LOANTYPE = '15' OR
LOANTYPE = '16' OR
LoanType = '17') AND
LoanStatus = 'Open') AND
ProcessDate = (CONVERT(VARCHAR, GETDATE(), 112)-1)) AND
(SUBSTRING (loandescription,1,4) not like '%[^0-9]%')
ORDER BY AccountNumber
Upvotes: 4
Views: 36889
Reputation: 3892
Since we don't know which RDBMS you are using, I'm going to go with the simplest answer. This assumes you're using MSSQL.
Use the ISNUMERIC() function to determine if SUBSTRING(loandescription,1,4)
is actually a number. If it is, then you can cast/convert it at that point and compare it to the "year" you're interested in. IE:
...
ProcessDate = (CONVERT(VARCHAR, GETDATE(), 112)-1)) AND
(ISNUMERIC(SUBSTRING(loandescription,1,4)) = 1 AND
CAST(SUBSTRING(loandescription,1,4) AS INT) = (YEAR(GETDATE()) - 25))
Due to boolean short circuiting, if the first 4 characters AREN'T numeric, then it won't bother casting to compare. Same rule applies with other RDBMS systems (such as MySQL, PostgreSQL, SQLite, etc), but the methods might be a bit different. In fact, I don't think MySQL or PostgreSQL even have the ISNUMERIC
function, meaning you need to find other ways (Regex) to test.
Upvotes: 3
Reputation: 34784
Assuming your 'YYYY' field is (SUBSTRING (loandescription,1,4) not like '%[^0-9]%')
:
AND CASE WHEN SUBSTRING (loandescription,1,4) not like '%[^0-9]%'
THEN CAST(SUBSTRING(loandescription,1,4)AS INT
ELSE YEAR(GETDATE())
END < YEAR(DATEADD(year,-25,GETDATE()))
You need the CASE
statement since the year isn't always populated, and text values won't CAST
as INT
. Then you use the GETDATE()
function in conjunction with DATEADD()
to add in your 25 year criteria.
Upvotes: 1