-5

I am doing a clean up of a database but I am getting this error. I was to delete data less than 6 months old.

Query: 

Delete from dbo.Jobs 
where dtpDate <= '2016-04-27 15:23:00.000'

Error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The type is datetime

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
suni_apprentice
  • 1
  • 3
  • What is type of `dtpDate` ? – demo Apr 29 '16 at 09:55
  • http://dba.stackexchange.com/questions/86313/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of check this question – demo Apr 29 '16 at 09:56
  • in order for us to properly answer your question, we need to know the format of dtpDate, so please do an echo of a date or two of type dtpDate so that we can compare the formats. – Webeng Apr 29 '16 at 09:59

3 Answers3

1
DELETE FROM dbo.Jobs 
      WHERE dtpDate <= CAST('2016-04-27 15:23:00.000' AS DATETIME)
drneel
  • 2,887
  • 5
  • 30
  • 48
Richard Boyce
  • 413
  • 5
  • 12
1

Try adding a cast to the query.

CAST( '2016-04-27 15:23:00.000' AS DATETIME)
aspirant_sensei
  • 1,568
  • 1
  • 16
  • 36
1

Your server probably has date format settings that are incompatible with yyyy-mm-dd. Check your current dateformat setting with dbcc useroptions. If it is incorrect, then change it with set dateformat.

The best solution is to make all queries independent of the current date format, e.g.

  • The ODBC locale-independent escape sequence

    delete from dbo.Jobs where dtpDate <= {ts'2016-04-27 15:23:00.000'}

  • The ODBC convert() format

    delete from dbo.Jobs where dtpDate <= convert(datetime, '2016-04-27 15:23:00.000', 120)

  • Use parameters when issuing SQL commands from an application.

Christian Hayter
  • 30,581
  • 6
  • 72
  • 99