SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error


Error

Author
Message
immaduddinahmed
immaduddinahmed
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 420
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
g_demetriou
g_demetriou
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 183
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?
immaduddinahmed
immaduddinahmed
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 420
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
g_demetriou
g_demetriou
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 183
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?
g_demetriou
g_demetriou
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 183
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
immaduddinahmed
immaduddinahmed
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 420
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
g_demetriou
g_demetriou
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 183
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.
g_demetriou
g_demetriou
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 183
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
immaduddinahmed
immaduddinahmed
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 420
your idea is working thanks sir
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search