• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Cannot delete row due to: out-of-range smalldatetime value

I'm having a problem with mssql. I accidentally inserted a value into a smalldatetime field using getdate(). When I try to delete the row using queries like

delete from tborders where nOrderID=1

or

delete from tborders where Convert(Varchar,dteOrderDate,103) = '22/03/2006'

It gives me the error;

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Is there another delete statement I could use to get rid of this row?

Thanks in advance for your help.
0
PsycheBNP
Asked:
PsycheBNP
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
delete from tborders
where dteOrderDate >= convert(smalldatetime, '22/03/2006', 103)
AND dteOrderDate < dateadd(day, 1, convert(smalldatetime, '22/03/2006', 103) )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
PsycheBNP,
> delete from tborders where Convert(Varchar,dteOrderDate,103) = '22/03/2006'

SET DATEFORMAT DMY
delete from tborders where Convert(Varchar(10),dteOrderDate,103) = '22/03/2006'
0
 
PsycheBNPAuthor Commented:
Thanks for the fast reply angelIII, unfortunately your query returned the same error.
0
 
PsycheBNPAuthor Commented:
That did the trick aneeshattingal, thank you very much.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now