Joining/concatenating fields - 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

General SQL Server Forums

New to SQL Server Programming

Joining/concatenating fields

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-06-13 : 07:32:55

I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?

My query and my desired results are below:

select item, test_date 
from inventory 
where cat = 'TELE' and itemised_status > 15

item                 test_date			cert_no
-------------------- -----------------------	-----------------
05MC0002             2014-06-10 00:00:00	05MC0002-06-2014
06MT0001             2014-05-13 09:02:00	06MT0001-05-2014
06MT0002             2014-05-13 09:03:00	06MT0002-05-2014
06MT0003             2014-05-13 09:03:00	06MT0003-05-2014
06MT0004             2014-05-09 14:12:00	06MT0004-05-2014

Many thanks
Martyn

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-13 : 07:39:12

[code]
select item, test_date, item + right('00'+cast(month(test_date) as varchar(2)), 2) + cast(year(test_date) as char(4))
from inventory
where cat = 'TELE' and itemised_status > 15
[/code]

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2014-06-13 : 08:55:42

That worked perfectly - thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources