Conversion failed when converting date and/or time from character string. when filtering sql select query

Question

I have a relatively basic query on which dates that are saved in a table as nvarchar(200).

I am trying to do the filter on an InteractionDate fields that looks like this

'02-03-2018 12:00', '03-04-2018 14:46', '03-04-2018 14:44' etc.

But get the error Conversion failed when converting date and/or time from character string. when trying to convert the nvarchar date field.

This is what the query looks like

 select
 act.InteractionDate,
 act.Status
 from JobCanvas_B2B canvas
  inner join PersonActivity_JobCanvas inters on inters.CanvasId = 
  canvas.CanvasId
   inner join PersonActivity act on act.PersonActivityId = 
    inters.PersonActivityId 
    inner join Stage s on s.StageId = act.StageId
     where convert(date, act.InteractionDate, 101) > convert(date, '01-01-2018 12:00', 101)

How could I do this date conversion correctly so that the query works?

Answer 1

If you can tolerate dealing with only the date component, then use mask 103 as you did in your answer. If you also need the time component, then we can try going through format mask 120, with some string manipulation along the way:

CONVERT(datetime, SUBSTRING(act.InteractionDate, 7, 4) + '-' +
     SUBSTRING(act.InteractionDate, 4, 2) + '-' + LEFT(act.InteractionDate, 2) +
     ' '  + RIGHT(act.InteractionDate, 5), 120)

Demo

Ideally, we should be able to use mask 131 directly, but I could not get it working, at least not with the type of data you have. Instead, the above snippet manually builds a timestamp of the format yyyy-mm-dd hh:mi:ss.

The best long term solution here is to not store date information as text. If you must do that, then you an ISO format which is easy to convert with SQL Server's built in functions.