Select Statement - 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

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-10-19 : 04:14:32

Hi,

I have the following SQL statement which yields;

SELECT     dbo.TblChangeType.TypeCode, dbo.TblChangeControlDet.ChangeRaisedDate, dbo.TblUser.Initials
FROM         dbo.TblChangeControlDet INNER JOIN
                      dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc INNER JOIN
                      dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name

Typecode ChangeRaisedDate Intials
AppE 18/10/12 00:00:00 RA
AppF 18/10/12 00:00:00 AA
DataFix 18/10/12 00:00:00 YG

I want to have the results as

RefNo

AppE/RA181012
AppF/AA181012
DataFix/YG181012

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 04:38:09

Hi,


SELECT     dbo.TblChangeType.TypeCode + '/' +  dbo.TblUser.Initials
 + REPLACE(CONVERT(VARCHAR(8), dbo.TblChangeControlDet.ChangeRaisedDate, 3), '/', '') 
FROM dbo.TblChangeControlDet 
INNER JOIN dbo.TblChangeType ON dbo.TblChangeControlDet.ChangeType = dbo.TblChangeType.TypeDesc 
INNER JOIN dbo.TblUser ON dbo.TblChangeControlDet.RequestBy = dbo.TblUser.user_full_name

If your Year part is four digits, Use
REPLACE(CONVERT(VARCHAR(10),dbo.TblChangeControlDet.ChangeRaisedDate, 103), '/', '')

--Chandu

xloafery
Starting Member

5 Posts

Posted - 2012-10-19 : 05:04:13

select ct.TypeCode + '/' + u.Initials + select cast(datepart(day, getdate()) as char(2)) + cast(datepart(month, getdate()) as char(2)) + right('0000' + convert(char(4),year(getdate())),2) as RefNo
FROM dbo.TblChangeControlDet as ccd
INNER JOIN dbo.TblChangeType as ct ON ccd.ChangeType = ct.TypeDesc
INNER JOIN dbo.TblUser as u ON ccd.RequestBy = u.user_full_name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:47:00

whats datatype of ChangeRaisedDate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources