Error converting date and time Query SQL Server

Question

Error converting date and time Query SQL Server

Navigation

#1 by (0 votes)

0

Well, I'm trying to run the query below, however, the following error is occurring. "Failed to convert string date and / or time.". I have already done some other conversions, but without success.

select
    MesID,Mes,Ano,
cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time), horas_uteis))/60 as nvarchar(3)) + ':' + cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time), horas_uteis))%60 as nvarchar(2))
 as horas
from (
    select
        dbo.FN_CALC_HORAS_UTEIS(s.SolDataFechamento,min(l.LogData)) as horas_uteis,
        datepart(month,s.SolDataFechamento) MesID,
        datename(month,s.SolDataFechamento) Mes,
        datepart(year,s.SolDataFechamento) Ano
    from Solicitacao S
        left join usuario U on (U.UsuID = S.UsuIDResponsavel) 
        left join Status ST on S.SolStatus = ST.CodStatus
        left join Log L on L.LogSolID = s.SolID and (l.LOGDESCRICAO like '%1057%' or l.LOGDESCRICAO like '%3343%')
    where
        S.proid in (2)
        and S.UsuIDResponsavel in (1776)
        and s.SolStatus = 9
        and convert(date,s.SolDataFechamento) between '01-01-2018' and getdate()--and s.SolID = 65513
        group by s.SolDataFechamento
) as X
group by MesID, Mes, Ano

sql sql-server query

asked by anonymous 29.01.2018 / 18:46

1 answer

Problems with mongoose searches Script returning Parse error: syntax error, unexpected end of file

score 0 · Accepted Answer

You can use CONVERT (DATE, FieldName , 105) because it uses the date in Italian format dd-mm-yyyy link

select
    MesID,Mes,Ano,
    cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time), horas_uteis))/60 as
    nvarchar(3)) + ':' + cast(SUM(DateDiff(MINUTE,  CAST('0:00' as time),horas_uteis))%60 as nvarchar(2))
as horas
from (
select
    dbo.FN_CALC_HORAS_UTEIS(s.SolDataFechamento,min(l.LogData)) as horas_uteis,
    datepart(month,s.SolDataFechamento) MesID,
    datename(month,s.SolDataFechamento) Mes,
    datepart(year,s.SolDataFechamento) Ano
from Solicitacao S
    left join usuario U on (U.UsuID = S.UsuIDResponsavel) 
    left join Status ST on S.SolStatus = ST.CodStatus
    left join Log L on L.LogSolID = s.SolID and (l.LOGDESCRICAO like '%1057%' or l.LOGDESCRICAO like '%3343%')
where
    S.proid in (2)
    and S.UsuIDResponsavel in (1776)
    and s.SolStatus = 9
    and convert(date,s.SolDataFechamento,105) between '01-01-2018' and convert(date,getdate(),105) --and s.SolID = 65513
    group by s.SolDataFechamento
) as X
group by MesID, Mes, Ano