CODEWITHSUNDEEP
sqlsql-server
Leon Huang
Leon Huang

Reputation: 103

TSQL Conversion failed when converting date and/or time from character string

    declare @startTime datetime,@strquery nvarchar(max)
    set @startTime = '2016-01-01 10:00:00.000'      
    set @strquery =RIGHT(CONVERT(char(23),@startTime),12)
    print @strquery
    --print result: 10:15AM

    set @strquery= '2016-01-01 '+RIGHT(CONVERT(char(23),@startTime),12)
    print @strquery
    --print result: 2016-01-01  10:15AM 

    set @strquery ='2016-01-01'+RIGHT(CONVERT(char(23),@startTime),12)
    print @strquery
    --print result: 2016-01-01 10:15AM

    set @strquery =CONVERT(datetime, '2016-01-01'+RIGHT(CONVERT(char(23),@startTime),12))
    print @strquery
    --print result:Jan  1 2016 10:15AM


     set @strquery ='select * from OneTransfer where CONVERT(datetime, QUOTENAME(2016-01-01,'''') +RIGHT(CONVERT(char(23),[FirstBoardTime]),12)) > ' 
     + QUOTENAME(CONVERT(nvarchar(100),@startTime),'''') +' order by FirstBoardTime'
    print @strquery
    --print result:select * from OneTransfer where CONVERT(datetime, QUOTENAME(2016-01-01,'') +RIGHT(CONVERT(char(23),[FirstBoardTime]),12)) > 'Jan  1 2016 10:15AM' order by FirstBoardTime        

    --The following line failed
    execute sp_executesql  @strquery

I got this error message:

Conversion failed when converting date and/or time from character string.

Because I need to compare hour and minute. So I transfer All of time value to the same date ex:2016-01-01 then I can find what I need

For example find all of day of time 10:15AM

It is my Table Data

Upvotes: 0

Views: 2488

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

The issue is this: CONVERT(datetime, QUOTENAME(2016-01-01,''). Besides a missing closing parenthesis, what you want to execute is: CONVERT(datetime, '2016-01-01').

To achieve this you have to get the quotename outside of the quotes. But I don't think you need it at all.

Try this:

declare @startTime datetime,@strquery nvarchar(max)
    set @startTime = '2016-01-01 10:00:00.000'      

SET @strquery ='select * from OneTransfer where CONVERT( time, FirstBoardTime) > ''' 
     + CONVERT(VARCHAR(8), CONVERT( time,  + @startTime)) + ''' order by FirstBoardTime'

SELECT @strquery

Returns:

select * 
from   OneTransfer 
where  CONVERT( time, FirstBoardTime) > '10:00:00' 
order by FirstBoardTime

Which is I think what you are looking for. This takes the date part of the equation. If you really do need it, I think this solution will give you enough to work with.

Upvotes: 1

Related Questions