June 7, 2013 at 11:23 pm
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
immad
June 10, 2013 at 3:32 am
immaduddinahmed (6/7/2013)
i am having this errorMsg 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?
June 10, 2013 at 4:46 am
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
immad
June 10, 2013 at 4:55 am
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?
June 10, 2013 at 5:31 am
immaduddinahmed (6/10/2013)
this is my select queryselect
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
June 10, 2013 at 6:18 am
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
immad
June 10, 2013 at 6:56 am
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.
June 10, 2013 at 7:07 am
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
June 10, 2013 at 11:18 pm
your idea is working thanks sir
immad
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy