SELECT [IDNum] , CASE WHEN LEFT([IDNum],2) BETWEEN '00' AND '20' THEN '20' ELSE '19' end AS CC , LEFT([IDNum],2) AS yy ,RIGHT(LEFT([IDNum],4),2) AS MM ,RIGHT(LEFT([IDNum],6),2) AS DD --,CAST( --CASE WHEN LEFT([IDNum],2) BETWEEN '00' AND '20' THEN '20' ELSE '19' end + -- LEFT([IDNum],2) + -- '-' + -- RIGHT(LEFT([IDNum],4),2) + -- '-' + -- RIGHT(LEFT([IDNum],6),2) -- AS date) FROM [Sales] WHERE [IDNum] IS NOT NULL and LEN(IDNum) = 13 and ISNUMERIC(IDNum) = 1 AND LEFT([IDNum],6) BETWEEN '000101' AND '991231' order by [IDNum]
Trying to calculate age from a field that carries a valid 13 digit ID number. however the same field carries companry registraiton numberwith all sorts of characters like "/" stroke and "-" hyphen
I went ahead and edited your post for readability. In the future when posting code, either enclose it in triple-backticks (
```) or highlight the code and hit the
</> button in the formatting toolbar (sixth button from the left)
Dealing with badly-formatted column data is pretty much par for the course with SQL data. You should see the awfulness that’s in public healthcare info database dumps. It’s the reason every decent database developer knows a language like perl, python, or ruby to massage the data (usually with regexes) before working with it. Which flavor of database are you using? You might have regexes built in, though they’ll never be pleasant to work with in raw SQL.