displaying year (YY) as YY-YY format

Question

Post reply

displaying year (YY) as YY-YY format

MuditGupta

SSCrazy

Points: 2596
More actions
October 21, 2008 at 8:04 am

#194780

I have an year column which displays year as YY format.
I want to display them in yy-yy format.
E.G
Yr NEW
9999-00
0000-01
0101-02
0202-03
0303-04
0404-05
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Is it always the year after?

Convert the year to a number if it's not, add one, convert to char, take the right 2 characters.

Use

CAST() and RIGHT()

MuditGupta SSCrazy Points: 2596 More actions · Answer 2

yes, it s always year after.

I have yr as Char(2).

As you suggested, I converted it to INT but then I get 01 as 1, 02 as 2 etc.. Which doesn't help me.

I got following:

Yr NEW

99 100

00 1

01 2

02 3

03 4

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

select

[Year] = right(a.Yr,2)+'-'+right(a.Yr+1,2)

from

( --Test Data

select Yr = 1998 union all

select Yr = 1999 union all

select Yr = 2000 union all

select Yr = 2001 union all

select Yr = 2002 union all

select Yr = 2004

) a

Results:

Year

-----

98-99

99-00

00-01

01-02

02-03

04-05

(6 row(s) affected)

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

cheap, may not be efficient way

DECLARE @test-2 TABLE (yr CHAR(2))

INSERT INTO @test-2

SELECT '99' UNION ALL

SELECT '00' UNION ALL

SELECT '01' UNION ALL

SELECT '02'

SELECT YearRange = yr + '-' + new

FROM (

SELECT *

,new=REPLICATE('0',2-LEN(RIGHT(yr+1,2))) + RIGHT(yr+1,2)

FROM @test-2

) T

Results

YearRange

99-00

00-01

01-02

02-03

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

noeld SSC Guru Points: 96590 More actions · Answer 5

DECLARE @test-2 TABLE (yr CHAR(2))

INSERT INTO @test-2

SELECT '99' UNION ALL

SELECT '00' UNION ALL

SELECT '01' UNION ALL

SELECT '02'

SELECT *

,yr + '-' + RIGHT('00'+ cast((cast(yr as tinyint)+1)%100 as varchar(2)),2) as New

FROM @test-2

* Noel

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Jerry Hung (10/21/2008)
cheap, may not be efficient way

That is pure speculation, unless you supply test results to back up that statement.

MuditGupta SSCrazy Points: 2596 More actions · Answer 7

I was doing it using some very ineffective way. I was using CASE and then was concatanating with either '19' or '20' ,followed by CAST commands.

Thanks for such wonderful and effective solutions.

cheers

Scott D-810830 SSC-Addicted Points: 494 More actions · Answer 8

Assuming that you had a tally table, you could do it like this. This allows you to easily make your range of years anything you want it to be.

SELECT n.num,

RIGHT('0' + CAST(n.num % 100 as varchar(2)), 2) +'-' + RIGHT('0' + CAST((n.num + 1) % 100 as varchar(2)), 2)

FROM Numbers n

WHERE n.num >= 1999 AND n.num <= 2004

Scott