Convert to Year(YYYY) using two digit

Hi,

I've a data feild as follows:

00
01
99
85
83
70

I need to get the full year format (YYYY) using SQL.I'm using SQL 2008. Any help would really appreciate.

Thanks.
keplanAsked:
Who is Participating?
 
keplanConnect With a Mentor Author Commented:
excellent
0
 
EyalCommented:
select cast ('20'+yearfield as int) year from ...

Open in new window

0
 
AmmarRCommented:
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

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
John_ArifinCommented:
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 ...
0
 
AmmarRCommented:
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
0
 
John_ArifinCommented:
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.
0
 
mcs0506Commented:
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
0
 
keplanAuthor Commented:
q
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.