Trim problem - 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

Trim problem

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-05-27 : 05:08:28

it gives me this error
Conversion failed when converting the varchar value '790-------' to data type int.

My procedure are like this

ALTER procedure [dbo].[SearchforEdit]
@EmpID NVARCHAR(50),
@FromDate DATETIME,
@ToDate DATETIME,
@CID NVARCHAR(50),
@BID NVARCHAR(50),
@DEPT NVARCHAR(50)
AS
BEGIN
SELECT
DISTINCT
RTRIM(T1.EID),
CONVERT(VARCHAR(10),Date,105) Date,
Date Date1,
TimeIn,
TimeOut,
T.Remarks,
T.Comments,
T1.ENAME,
T2.DName,
T3.NAme Designation,
T4.CID,
T4.CName,
T5.BID,
T5.BName
FROM
(
SELECT
EID,
Date,
TimeIn,
TimeOut,
Remarks,
Comments
FROM
FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept)
Where
(InCType = 2 OR OutCType = 2 OR Remarks = 'Absent')
AND Date BETWEEN @FromDate and @ToDate
AND (ISNULL(@EmpID,'')='' OR EID = @EmpID)
AND (ISNULL(@CID,'')='' OR CID = @CID)
AND (ISNULL(@BID,'')='' OR BID = @BID)

UNION

SELECT * FROM
(
SELECT
T1.EID,
T1.[Date],
T2.TimeOut AS Timein,
T1.TimeIn AS TimeOut,
T1.Remarks,
T1.Comments
FROM
FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1
LEFT OUTER JOIN FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T2 ON T2.EID = T1.EID AND T2.TimeOut <> T1.TimeIN AND T2.Date = T1.Date AND T2.rn = T1.rn - 1
Where
T1.TimeIn IS NOT NULL
AND T1.Date BETWEEN @FromDate and @ToDate
AND (ISNULL(@EmpID,'')='' OR T1.EID = @EmpID)
AND (ISNULL(@CID,'')='' OR T1.CID = @CID)
AND (ISNULL(@BID,'')='' OR T1.BID = @BID)

)D
Where
TimeIn IS NOT NULL

UNION

SELECT
RTRIM(T1.EID),
Date,
Case When rn = 1 AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTime,108) THEN
DateAdd(d, DateDiff(d, 0, Cast(CONVERT(VARCHAR(8),Date,112) As datetime)), Cast(CONVERT(VARCHAR(8),ShiftTimeIn,108) as datetime))
ELSE TimeIn
END TimeIn,

Case When
rn = 1
AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTime,108) THEN TimeIn
ELSE NULL END TimeOut,
Remarks,
Comments
FROM
FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T
LEFT OUTER JOIN Employee T1 ON T.EID = T1.EID
LEFT OUTER JOIN Designation T2 ON T1.CID = T2.CID AND T1.BID = T2.BID AND T1.DesignationID = T2.DesignationID

Where
(InCType = 0) AND (T2.LateExcemption = 0 AND CONVERT(VARCHAR(8),TimeIn,108) > CONVERT(VARCHAR(8),LTIME,108))
AND CONVERT(VARCHAR(8),T.TimeIn,108) > CONVERT(VARCHAR(8),T.LTIME,108) AND rn = 1
AND T.Date BETWEEN @FromDate and @ToDate
AND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)
AND (ISNULL(@CID,'')='' OR T.CID = @CID)
AND (ISNULL(@BID,'')='' OR T.BID = @BID)

UNION

SELECT
RTRIM(T1.EID),
Date,
Case When rn = 1
AND CONVERT(VARCHAR(8),TimeOut,108) < CONVERT(VARCHAR(8),ShiftTimeOut,108)
THEN TimeOut
ELSE NULL
END TimeIn,

Case When
rn = 1
AND CONVERT(VARCHAR(8),TimeOut,108) < CONVERT(VARCHAR(8),ShiftTimeOut,108)
THEN ShiftTimeOut
ELSE NULL
END TimeOut,
Remarks,
Comments
FROM
FE4 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T
LEFT OUTER JOIN Employee T1 ON T.EID = T1.EID
LEFT OUTER JOIN Designation T2 ON T1.CID = T2.CID AND T1.BID = T2.BID AND T1.DesignationID = T2.DesignationID
Where
(InCType = 0) AND CONVERT(VARCHAR(8),T.TimeOut,108) < CONVERT(VARCHAR(8),T.ShiftTimeOut,108) AND rn = 1
AND T.Date BETWEEN @FromDate and @ToDate
AND (ISNULL(@EmpID,'')='' OR T.EID = @EmpID)
AND (ISNULL(@CID,'')='' OR T.CID = @CID)
AND (ISNULL(@BID,'')='' OR T.BID = @BID)

)T
LEFT OUTER JOIN Employee T1 ON T.EID = T1.EID
LEFT OUTER JOIN Department T2 ON T1.DID = T2.DID
LEFT OUTER JOIN Designation T3 ON T1.CID = T3.CID AND T1.BID = T3.BID AND T1.DesignationID = T3.DesignationID
LEFT OUTER JOIN Company T4 ON T1.CID = T4.CID
LEFT OUTER JOIN Branch T5 ON T1.BID = T5.BID
END

----------------------------------------------------------------

my function is like this

ALTER FUNCTION [dbo].[FE4]
(
@CID int,
@BID int,
@EmpID int,
@FromDate Datetime,
@ToDate Datetime,
@Dept VARCHAR
)
RETURNS TABLE
AS
RETURN
SELECT
--RTRIM(replace(T1.EID, ' ', '')) EID,
RTRIM(T1.EID) EID,
T2.CID,
T2.BID,
T1.Date,
T1.TimeIn,
T1.INCTYPE,
T1.TimeOut,
T1.OUTCTYPE,
T1.ShiftTimeDiff,
T1.DutyHours,
T1.ShiftTimeIn,
T1.ShiftTimeOut,
T1.LTime,
T1.HDTime,
T1.EarlyArival,
T1.Remarks,
T1.Comments,
rn = ROW_NUMBER() OVER (PARTITION BY RTRIM(T1.EID),DATEADD(dd, 0, DATEDIFF(dd, 0, T1.Date))
ORDER BY T1 .TimeIN )
FROM FE3 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1
LEFT OUTER JOIN Employee T2 ON T1.EID = T2.EID

------------------------------------

second function is like this

ALTER FUNCTION [dbo].[FE3]
(
@CID int,
@BID int,
@EmpID int,
@FromDate Datetime,
@ToDate Datetime,
@Dept VARCHAR
)
RETURNS TABLE
AS
RETURN

SELECT
T.CID,
T.BID,
T.EID,
T.Date,
T1.TimeIN,
T1.INCTYPE,
T1.TimeOut,
T1.OUTCType,
CASE
WHEN T2.SName = 'O' THEN 'Off Day'
WHEN T1.Timein IS NULL THEN 'Absent'
ELSE T1.Remarks
END Remarks,
T1.Comments,
T2.SName ShiftName,
T3.TimeIn ShiftTimeIn,
T3.TimeOut ShiftTimeOut,
T3.LTime,
T3.HDTime,
T3.EarlyArival,
T3.ShiftTimeDiff,
T3.DutyHours
FROM
FCalendarView (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T
LEFT OUTER JOIN FE2 (@CID,@BID,@EmpID,@FromDate,@ToDate,@Dept) T1 ON T.EID = T1.EID AND T.Date = T1.Date
LEFT OUTER JOIN FRoaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) T2 on T.EID = T2.EID AND DATEPART(YYYY,T.DATE) = T2.Year AND DATEPART(MM,T.DATE) = T2.Month AND DATEPART(DD,T.DATE) = T2.Day
LEFT OUTER JOIN ShiftView T3 ON T2.SName = T3.Shift

can any one tell me what is the problem

immad uddin ahmed

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 05:18:52

May be somewhere you have alphanumeric value....
add WHERE condition as follows:
WHERE ISNUMERIC(ColumnName) = 1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 05:21:13

i think issue is your EID field. If your attempt is to remove --- characters from it use REPLACE and not RTRIM.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources