Error

  • 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

  • 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?

  • 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

  • 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?

  • 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

  • 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

  • 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.

  • 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

  • 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