• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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.
0
keplan
Asked:
keplan
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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:
excellent
0
 
keplanAuthor Commented:
q
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now