combine tax year and month to 4 digit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

combine tax year and month to 4 digit

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-07 : 08:38:52

Hello,

I want to combine 4 digit tax year, and 2 digit month into 4 digit. For example, tax year=2005, and month=12. I want it comes out = "0512". How would I do it. Below is my initial try, but it cames out 6 digit=200512.

((TAX_YEAR * 100) + FILE_MONTH) AS per

Please show me how to make this work..thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:44:11

SELECT RIGHT(TaxYear, 2) + REPLACE(STR(Month, 2, 0), ' ', '0') AS Per

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 08:44:37

SELECT RIGHT(100 * Year + Month, 4)

E 12°55'05.63"
N 56°04'39.26"

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-07 : 08:55:44

The below won't work because data type is numeric. Should we use trancate function? Thanks,

quote:
Originally posted by Peso

SELECT RIGHT(100 * Year + Month, 4)

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:03:28

SELECT RIGHT(100 * cast(year as int)+ cast(month as int), 4)

Madhivanan

Failing to plan is Planning to fail

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-07 : 09:09:57

I got error message saying that "First argument to right must be binary or string"...

quote:
Originally posted by madhivanan

SELECT RIGHT(100 * cast(year as int)+ cast(month as int), 4)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:12:08

quote:
Originally posted by nt4vn

The below won't work because data type is numeric.

Try first and complain later, please

-- Integer
DECLARE	@Year SMALLINT,
	@Month TINYINT

SELECT	@Year = 2005,
	@Month = 12

SELECT	RIGHT(100 * @Year + @Month, 4)

-- Numeric
DECLARE	@Year2 NUMERIC(4),
	@Month2 NUMERIC(2)

SELECT	@Year2 = 2005,
	@Month2 = 12

SELECT	RIGHT(100 * @Year2 + @Month2, 4)

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:14:08

quote:
Originally posted by Peso

quote:
Originally posted by nt4vn

The below won't work because data type is numeric.

Try first and complain later, please
-- Integer
DECLARE @Year SMALLINT,
@Month TINYINT

SELECT @Year = 2005,
@Month = 12

SELECT RIGHT(100 * @Year + @Month, 4)

-- Numeric
DECLARE @Year2 NUMERIC(4),
@Month2 NUMERIC(2)

SELECT @Year2 = 2005,
@Month2 = 12

SELECT RIGHT(100 * @Year2 + @Month2, 4)

E 12°55'05.63"
N 56°04'39.26"

If Numeric has precision, it wont work

DECLARE @Year2 NUMERIC(10,2),
@Month2 NUMERIC(10,2)

SELECT @Year2 = 2005,
@Month2 = 12

SELECT RIGHT(100 * @Year2 + @Month2, 4)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:17:00

That's not the case.
OP says he got error message "First argument to right must be binary or string".
When using my suggestion and change data type numeric with precision greater than 0, you will get error message "Arithmetic overflow error converting int to data type numeric.".
They are not the same.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-07 : 09:26:07

[code]-- Numeric2
DECLARE @Year2 NUMERIC(6, 2),
@Month2 NUMERIC(4, 2)

SELECT @Year2 = 2005,
@Month2 = 12

SELECT RIGHT(STR(100 * @Year2 + @Month, 6, 0), 4)[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 09:26:12

quote:
Originally posted by Peso

That's not the case.
OP says he got error message "First argument to right must be binary or string".
When using my suggestion and change data type numeric with precision greater than 0, you will get error message "Arithmetic overflow error converting int to data type numeric.".
They are not the same.

E 12°55'05.63"
N 56°04'39.26"

Yes. Seeing the error message, I doubt if OP is using SQL Server

Madhivanan

Failing to plan is Planning to fail

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2009-04-07 : 09:46:10

I got it work as below code:
right(cast((tax_year * 100) + file_month as varchar(6)),4) as per

Thank you all!

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

That's not the case.
OP says he got error message "First argument to right must be binary or string".
When using my suggestion and change data type numeric with precision greater than 0, you will get error message "Arithmetic overflow error converting int to data type numeric.".
They are not the same.

E 12°55'05.63"
N 56°04'39.26"

Yes. Seeing the error message, I doubt if OP is using SQL Server

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources