Convert to Year(YYYY) using two digit

select cast ('20'+yearfield as int) year from ...

Select allOpen in new window

hi keplan

you need to specify the year change

i mean for value 70 it should be 1970 and for value 01 it should be 2001

so

create table #yt
(
y int
)

insert into #yt
select 00 union all
select 01 union all
select 99 union all
select 85 union all
select 83 union all
select 70 


select [YEAR] = case
when Y >= 70 then 
	1900 + Y
	else
	2000 + Y
	end
	from #yt

Select allOpen in new window

I assume that the 99 to 70 will use 19.. and the 00 to 01 will use 20.. and they are the string data type.
I propose the query could be changed to ::
select cast (case when cast(yearfield  as int) >= 70 then '19'+ yearfield  else '20'+ yearfield end as int) year from ...

dear Eyal: & John_Arifin:

adding '20' + yearfield

will make the year 00 or 01 as 200 and 201

so you need to add 2000 + yearfield to make sure you get the right year....

Regards

As long as the data are string(varchar), it is OK to append it next to the 19 or the 20.
In AmmarR's code the data are inserted into the int, then it is needed to add with 1900 or to 2000.

Hi,
Here is scalar value function that convert your year value to YYYY format with respect to current century for example if current year is 2050 the this function convert  0-50 to 2000-2050 and greater  value to be convert into 1951-1999. this is also work for future date also may be 2150.
Here is the function


CREATE       FUNCTION [dbo].[GetYearValue] ( @year INT )
RETURNS INT
    BEGIN
        DECLARE @CurrentYear INT
        DECLARE @FirstPart INT
        DECLARE @yr NVARCHAR(2)
        IF @year <= 9
            SET @yr = '0' + CONVERT(NVARCHAR(2), @year)
        ELSE
            SET @yr = CONVERT(NVARCHAR(2), @year)

        SET @FirstPart = CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(4), DATEPART(YEAR,
                                                              GETDATE())), 1,
                                                2))
        SET @CurrentYear = CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(4), DATEPART(YEAR,
                                                              GETDATE())), 3,
                                                  2))
        IF @year > @CurrentYear
            SET @FirstPart = @FirstPart - 1
   
        RETURN CONVERT(INT,CONVERT(NVARCHAR(2),@FirstPart)+@yr)

    END



You can test it by using following query


DECLARE  @tempyear TABLE(y int)

insert INTO @tempyear( y )VALUES ( 00 )
insert INTO @tempyear( y )VALUES ( 01 )
insert INTO @tempyear( y )VALUES ( 99 )
insert INTO @tempyear( y )VALUES ( 85 )
insert INTO @tempyear( y )VALUES ( 83 )
insert INTO @tempyear( y )VALUES ( 70 )



SELECT dbo.GetYearValue(y)AS years FROM @tempyear

q