CONCATENATION YEAR() + STRING - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

CONCATENATION YEAR() + STRING

Author

Topic

doco
Yak Posting Veteran

77 Posts

Posted - 2009-02-20 : 13:54:40

In a table there is a field pxfer_date that is datetime type.

If a value in this field is '2000-02-01 00:00:00.000' and a query is ran against that field


    select YEAR( pxfer_date ) as tax_year
    from table

returns as expected: 2000

Now then why does


    select cast( YEAR( pxfer_date ) + '0101' as INTEGER ) as eff_date
    from table

not return the expected: 20000101 but: 2101?

TIA

Education is what you have after you've forgotten everything you learned in school

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 13:58:43

Because you have converted it to Int.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-20 : 13:59:24

It is adding them together rather than concatenating them. You'll need to convert to varchar/char to concatenate them first.

select CONVERT(int, CONVERT(char(4), YEAR(pxfer_date)) + '0101') as eff_date
from table

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 13:59:33

[code]select cast( YEAR( pxfer_date ) + '0101' as varchar(20)) as eff_date
from table[/code]

doco
Yak Posting Veteran

77 Posts

Posted - 2009-02-20 : 14:13:29

quote:
Originally posted by tkizer

It is adding them together rather than concatenating them. You'll need to convert to varchar/char to concatenate them first.

select CONVERT(int, CONVERT(char(4), YEAR(pxfer_date)) + '0101') as eff_date
from table

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Returns 20010101
Works swimmingly Thanks

quote:

select cast( YEAR( pxfer_date ) + '0101' as varchar(20)) as eff_date
from table

Returns 2101.

Thanks all.

Education is what you have after you've forgotten everything you learned in school

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 14:17:12

Sorry it should be:

Select cast(YEAR(pxfer_date) as varchar(20)) + '0101'  
from table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-20 : 14:22:01

quote:
Originally posted by doco

quote:
Originally posted by tkizer

It is adding them together rather than concatenating them. You'll need to convert to varchar/char to concatenate them first.

select CONVERT(int, CONVERT(char(4), YEAR(pxfer_date)) + '0101') as eff_date
from table

Returns 20010101
Works swimmingly Thanks

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-21 : 02:02:42

If you are trying to return first day of that year

SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,PXFER_DATE),0) AS TAX_YEAR FROM TABLE

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources