question

CEO-8149 avatar image
0 Votes"
CEO-8149 asked MelissaMa-msft commented

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

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?130760-sqlinsertcool.jpg


130872-sqlinsertcool.jpg


sql-server-generalsql-server-transact-sqlazure-sql-database
sqlinsertcool.jpg (53.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What exactly is the delete command you are running?

Why is dateRecorded a varchar(500)? Is there a reason it is not a datetime datatype? Fields should have the correct data types assigned to them in the database.

0 Votes 0 ·

Maybe there is a trigger that raises this error?

Are you deleting the rows manually in Management Studio?

0 Votes 0 ·

I actually first wanted to delete the row through the SQL server, when it started throwing that error, I decided to use my app program and it also threw the error as a Microsoft error

0 Votes 0 ·

And how does your DELETE statement look like?
Which SQL Server version are you using?

0 Votes 0 ·

Theoretically, any date and time can be converted to string. If possible, show more details about your SQL and application code, and show the full error message.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered CEO-8149 edited

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
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your contribution.
The reason I used varchar as the data type of the dateRecorded was because in my application, I created a textfield for date so that users can be seeing the date while working before submitting the records.

The textfield can't accept date data type, it has to be converted to string for it to appear in the textfield. That was the reason I have to make the data type in the table varchar.

Again, I can make the data type in the table DATETIME and then don't let the datetime appear in the form while users are working on it.

Before today, this program has been working fine in this table, I was surprised when the error showed up.

I have other tables and programs where the data type is varchar, I'm not having any issue with them during insertion, updating or deletion.

Mr. @TomPhillips-1744 , the command is DELETE FROM myTable where formReqNo = ***

Again, I added a column for serial number and also used that as a condition for deletion. No one worked.

Meanwhile I run this same program in my other system, everything works fine and I could delete records.

@ErlandSommarskog I will try to use your suggestion to see what's wrong. I will give you a feedback as from 8hours from now. It's late here already

0 Votes 0 ·

What controls you have in your form has nothing to do with what data types you have in the database. The data layer and presentation layer are separate entities. But normally, you have a date-picker or similar control dor dates. But you can have a free-text input, if you think that is better. The database column should still be datetime2(3).

Meanwhile I run this same program in my other system, everything works fine and I could delete records.

That is only because you have been lucky, and you have not gotten any bad data yet. A design like this is an accident waiting to happen.

0 Votes 0 ·

Do you know I can't even alter and modify the datatype of the daRecorded column unless I drop the column?

0 Votes 0 ·

Hello @ErlandSommarskog , I just run the first code you gave on my sql server, it runs without producing anything apart from the column names.

0 Votes 0 ·

I guess then that it is time that you post the complete error message you get wnen you run the command from SSMS.

Nevertheless, you should fix your table design,

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @CEO-8149,

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.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, I tried your ISDATE(dateRecorded) = 0, it runs without any issue, it outputs the records in the table as they are.

0 Votes 0 ·

Hi @CEO-8149,

Thanks for your update.

It is recommend to provide more details about your table structure, sample data, trigger created on this table, application code and complete error message.

Or you could provide some details so that we could reproduce your issue and check further.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

I decided to save myself the stress by dropping the column, adding it with the dateRecorded data type as DATETIME and I made adjustment in my application program. If only I have enough time, I would have really love to see the reason a program that has been working suddenly stopped because of date issue. The worst is that it doesn't even allow one to delete record. That is too weird.

Thanks for your contribution.

@ErlandSommarskog , @TomPhillips-1744 , @Viorel-1

0 Votes 0 ·
Show more comments

You may have records other than date types in dateRecord varchar(500) which is not converting to date when you used in select where condition.

0 Votes 0 ·