Error convert Query SQL Server date and time

Asked

Viewed 2,207 times

0

Guys, I’m trying to run the query below, however, the following error is occurring. " Failed to convert string date and/or time.". I have 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

  • The default sql format for dates is 'yyyy-mm-dd', you should be getting the error because of this, since in your Where is presented a '01-01-2018'

  • Or anything tries a Convert

1 answer

1


You can use CONVERT(DATE, fieldname, 105) since it uses the date in the Italian format dd-mm-yyyy https://www.w3schools.com/sql/func_sqlserver_convert.asp 

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

  • ball show @Carlos T.Gomes.

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.