Mattaceten Mattaceten - 8 days ago 5
SQL Question

T-SQL how to get four digit year value using two digit year part

I have a string value 16******* that I call substring on to get the first two digits of the string literal

SUBSTRING(@CourtCaseNumber,1,2)

I then need a function that will take the string argument(16) and return to me the four digit year value, 2016. Some will be of 99, therefore i'll need a year value of 1999, 98 will need to be 1998, 15 will need to be 2015 etc.

So it should look something like this. I can't find a function that will do this for me.

NEEDEDFUNCTION(SUBSTRING(@CourCaseNumber,1,2)

Answer

So based on @Gordon Linoff answer function will look as below:

create function dbo.your_name
(
    @yearYY nvarchar(2)
)
RETURNS nvarchar(4)
as
begin
    declare @yearYYYY nvarchar(4)
    set @yearYYYY = (SELECT ((CASE WHEN @yearYY >= 50 THEN '19' 
                        ELSE '20' END) + @yearYY
                    ) as yyyy)
    return @yearYYYY
end

test:

select dbo.your_name ('70')
Comments