"Conversion failed when converting date and/or time to string.." whenever I try to delete records from this table

Question

Please I am having similar issue. I actually designed an application GUI, the GUI has a form with different input variables (textfield ). There's also a textfield for date and the date gets inputted automatically.

After all the data have been entered in their textfields of the form, I submit the record and they get saved in SQL database.

The database column for dateRecorded has data type of varchar(500).

So when application form is submitted, every column record gets saved in their respective columns in the database table.

I have no issue inserting and retrieving record. It's just about 3hours ago I discovered I'm unable to delete any record from the table. It comes up with an SQL error message that conversion failed when converting date and/or time.

I am so confused. I was wondering why a deletion process would have anything to do with conversion. I am about deleting the record, not making any transaction with it. Why is it giving me this error please?

I am attaching a photo showing a record with the stored dateRecord.

Meanwhile I've been deleting records from this same table before. I'm surprised it no longer works as it used it.

How do I resolve this please?

Answer 1

You get the error because you have made an incorrect database design. There are special data types for date and time values use them. Don't use strings. If you use strings, you will sooner get incorrectly formatted datetime values, or values that are out of range, like 2021-09-31 12:62:23.

You can find the bad values in your table with this query:

SELECT * FROM tbl WHERE try_cast(DateRecord AS Datetime2(3)) IS NULL

Once you have corrected the bad values, run this command:

ALTER TABLE tbl ALTER COLUMN DateRecord datetime2(3) NOT NULL

Answer 2

Hi @CEO ,

Welcome to Microsoft Q&A!

I made a test from my side and could not reproduce your issue.

create table mytable  
(formReqNo int,  
dateRecord varchar(500))  
  
insert into mytable values  
(588399021,'2021-08-09 13:47:36.373'),  
(588399021,'2021-08-09 13:47:37.513')  
  
DELETE FROM myTable where formReqNo = 588399021  

Above is working with no error.

I also added one more row with wrong date format and changed the where condition but it was still working with no error.

insert into mytable values  
(588399021,'2021-08-09 12:66:36.373')  
  
DELETE FROM myTable where dateRecord>='2021-08-09 13:00:37.513'  
  
DELETE FROM myTable where dateRecord between '2021-07-30 09:00:00' and '2021-09-30 09:00:00'  

So first of all, we have to correct all the date format of dateRecord column in your table.

Adding to what Erland mentioned, you could also try with below query to find out all incorrect formatted records.

select * from mytable where ISDATE(dateRecord)=0  

Besides, you could also try to change the SET DATEFORMAT, SET LANGUAGE and default language option settings to see any possibility.

Another way is to use the ISO-8601 date format that is supported by SQL Server which works always and regardless of your SQL Server language and dateformat settings. One format is YYYY-MM-DDTHH:MM:SS for dates and times.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.