Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Expand / Collapse
Author
Message
Posted Friday, June 7, 2013 11:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:48 AM
Points: 105, Visits: 324
i am having this error

Msg 257, Level 16, State 3, Procedure query, Line 21
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.


this is the line and red one is the column that i replace from 540


CAST (ABS( t4.LTime - DATEDIFF (MINUTE, t.[Timein], t.[Timeout] ) ) % 60 as varchar ) + ' min' as excesshort, -- Minutes


this column t4.LTime datatype is datetime

please help me out
Post #1461260
Posted Monday, June 10, 2013 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:32 AM
Points: 36, Visits: 150
immaduddinahmed (6/7/2013)
i am having this error

Msg 257, Level 16, State 3, Procedure query, Line 21
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.


this is the line and red one is the column that i replace from 540


CAST (ABS( t4.LTime - DATEDIFF (MINUTE, t.[Timein], t.[Timeout] ) ) % 60 as varchar ) + ' min' as excesshort, -- Minutes


this column t4.LTime datatype is datetime

please help me out


You are trying to use the ABS function with datetime data. It can only take numeric data. http://msdn.microsoft.com/en-us/library/ms189800.aspx

What are you trying to achieve?
Post #1461450
Posted Monday, June 10, 2013 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:48 AM
Points: 105, Visits: 324

this is my select query


select
CalDate,
Timein,
Timeout ,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime,
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar ) as excesstime_runtime,
CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short'
END as Excess,
from
#attendance

i want to update excess column written in select query


update
#attendance
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance


please suggest me a syntax
Thank you for the help
Post #1461474
Posted Monday, June 10, 2013 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:32 AM
Points: 36, Visits: 150
Is excessshort a varchar?

If so, a colon is a unicode character so change the field to nvarchar.


My mistake, that shouldn't cause the error.

What error are you getting when updating? Is it still the float error?
Post #1461476
Posted Monday, June 10, 2013 5:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:32 AM
Points: 36, Visits: 150
immaduddinahmed (6/10/2013)

this is my select query


select
CalDate,
Timein,
Timeout ,
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108) AS SpendTime_runtime,
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar ) as excesstime_runtime,
CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short'
END as Excess,
from
#attendance

i want to update excess column written in select query


update
#attendance
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108)
from
#attendance


please suggest me a syntax
Thank you for the help


If I understand you correctly, you want to update the [Excess] column.
To make it easy to read, you could declare a varchar variable. Then update it with the value you need. Then include it in your update statement. So something like:

DECLARE @excess varchar(6)

SET @excess = Select 'Then your CASE statement here

update
#attendance
set
excessshort =
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, Timein, timeout ) ) % 60 as varchar )-- Minutes
,-- Only here so visible
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Timein],[Timeout]),0),108),
Excess = @excess
from
#attendance
Post #1461490
Posted Monday, June 10, 2013 6:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:48 AM
Points: 105, Visits: 324
Sir my first problem about ABS function is solve


sir i change query u can see in bold fonts
but its give me error



Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.


ALTER procedure [dbo].[query]
(
@empid nvarchar(50)

)
as
begin
select
[date],
min([Timein]) as First_Record,
sum(DATEDIFF(ss, [Timein], [Timeout])) as Time_In_Seconds
into #temp1 from attendlog
where eid=@empid
group by [date]

select
t.[date],
e.ename,
t.eid,
t5.dname,
d.name designation,
T4.shift,
t.[Timein] timein,
t.[Timeout]timeout,
--t4.ltime Latetime,
--t4.HDTime,
CONVERT(VARCHAR(8), DATEADD(ss, Time_In_Seconds, 0), 108) AS SpendTime,
CAST (ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) / 60 as varchar ) + ' hrs : ' + -- Hours ( + ':' separator )
CAST (ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend) ) % 60 as varchar ) + ' min' as excesshort, -- Minutes

CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short' END Excess,

case when convert(varchar(10),t.[Timein],108) > convert(varchar(10),t4.ltime,108) then 'Late'

when convert(varchar(10),t.[Timein],108) is null and convert(varchar(10),t.[Timeout],108) is null and shift <> 'O' then 'Absent'
when t4.shift = 'O' then 'OFFDAY'
when t4.HDTime > t.[Timeout] and Excess = 'Short' then 'HalfDay'
else '' end Remarks

FROM attendi t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
--left join froaster (@EmpID,@FromDate,@ToDate,@CID,@BID,@Dept) r ON t.eid = r.eid
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where t.eid=@empid
order by t.[date]
DECLARE @excess varchar(6)


SET @excess =select CASE WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= t4.minute and t.[Timein] = t.[Timein] and t.[Timeout] = t.[Timeout] THEN 'Excess' else 'Short' end excess


update
attendlog
set
excessshort =
CAST ( ABS(convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) / 60 as varchar ) + ':' + -- Hours ( + ':' separator )
CAST ( ABS( convert(varchar(10),t4.minute,108) - DATEDIFF (minute, 0,spend ) ) % 60 as varchar ) -- Minutes
,
Spend =
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss, t.[Timein],t.[Timeout]),0),108)
,
Excess = @excess

from
attendi t
left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record
left join employee e on e.eid = t.eid
left join designation d on e.designationid = d.designationid
LEFT OUTER JOIN FRoaster (@EmpID) T3 ON e.EID = T3.EID AND DATEPART(YYYY,T.DATE) = T3.Year AND DATEPART(MM,T.DATE) = T3.Month AND DATEPART(DD,T.DATE) = T3.Day
LEFT OUTER JOIN ShiftView T4 ON T3.SName = T4.Shift
LEFT OUTER JOIN Department T5 ON T5.did = e.did
where t.eid=@empid
end

Post #1461506
Posted Monday, June 10, 2013 6:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:32 AM
Points: 36, Visits: 150
You need to break down your code into sections. Work through each part, 1 at a time to work out the issue.

Your CASE statement has your t. & t4. aliases, but no FROM part and not of the joins and aliases.

So either use variables, set them in your select statement then update using them, or put your code directly in the update statement.

Post #1461523
Posted Monday, June 10, 2013 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:32 AM
Points: 36, Visits: 150
So as an example you can do things like:

declare @id int,
declare @myVar nvarchar(10)

select @id = [id],
@myVar = [myColumn]
FROM Table1

Update Table2
Set MyColumn = @myVar
Where ID = @id
Post #1461528
Posted Monday, June 10, 2013 11:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 18, 2014 3:48 AM
Points: 105, Visits: 324
your idea is working thanks sir
Post #1461866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse