-1

I have a program that is moving data between two tables in a database. For this, I am using a SQL query and the System.Data.SqlClient package.

All of a sudden, this query throws an error when executing. SQLCommand.ExecuteNonQuery is throwing:

Conversion failed when converting date and/or time from character string.

I have isolated the line with the conversion to:

 AND DATEDIFF(dd,GETDATE(),SUBSTRING(a.date, 1, 10))<14

where a.date is the datetime as varchar. The query is a INSERT-SELECT query, and if I run only the SELECT part, it works. Even more strange is that this query works perfectly fine to run in SSMS both with and without data found.

Have anyone else seen this case lately?

jps
  • 20,041
  • 15
  • 75
  • 79
Pierre
  • 1
  • 1
    `SUBSTRING(a.date, 1, 10)` is not returning a value that SQL Server is interpreting as a valid date. Please show us the entire query. – mjwills Feb 26 '18 at 09:48
  • Do you know what the actual value of `a.date` is when this error occurs? I suspect @mjwills is correct. – Matt Hogan-Jones Feb 26 '18 at 09:49
  • Use SQL Profiler to know the final query – Christophe Debove Feb 26 '18 at 09:52
  • @mjwills - The a.date in this instance is most likely an empty varchar. However, this is not a problem when running the query in SSMS, and not even a problem if the query is only a SELECT in the core 2 application. This only occurs when the query is an INSERT-SELECT, and only then in the application. – Pierre Feb 26 '18 at 10:24
  • @mjwills - Sadly, i can't display the query as a whole, as it may contain company data. The question is, in fact, not why it does not work. The question is why it did work last week, and not now. No changes has been made to the code, and no new data has entered the queried table since then. – Pierre Feb 26 '18 at 11:33

2 Answers2

1

where a.date is the datetime as varchar.

well there's your main problem. If you are storing a date/time: use the appropriate storage type in the database. You have a wide range to choose from, for example date, smalldatetime, datetime and datetime2 - and: SQL Server will know how to correctly understand and work with that data.

Ultimately the problem here is that SUBSTRING(a.date, 1, 10) isn't giving a result that SQL Server understands as a date through implicit string conversion operations. This approach is a: inefficient, and b: brittle (especially between cultures), hence why you simply shouldn't do that. If you store the data appropriately: all the problems will go away.

However! You could also use CONVERT to tell SQL Server to interpret the string as a date/time, explicitly telling it the format you expect (as a number code), so that it stands a chance.

If your a.date (and substring) isn't in one of the supported formats: abandon all hope.

BTW; DATEDIFF(dd,GETDATE(),SUBSTRING(a.date, 1, 10))<14 is probably more efficiently done by way of calculating the start-date/end-date of your range once and just comparing with a comparison operator. GETDATE() won't change per row, so "14 days from now" won't change per row. This would make your query a lot more efficient, especially when combined with the correct date/time format - it becomes:

a.date <= @end -- or < @end, or > @end, or >= @end

which can use an index.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Thank you for all the tips. I do now that the sql query is outdated, and it is set up for refactoring. What i don't understand however, is why this has started happening now, and as it seems, only for the core 2 application. I have identical queries in both a .net 4 application and an old vb-script, and none of these have crashed, and the scripts have been running for close to a decade. – Pierre Feb 26 '18 at 10:18
  • @Pierre this error is happening at the server, not the client, so that shouldn't be a factor. Are you perhaps *storing* the dates in a different string format? – Marc Gravell Feb 26 '18 at 10:59
  • But isn't the client sending the Query in exact the same way regardless if i send it in an application as from SSMS? And why has it worked up until now? The query in the application have never had problem with a.date being varchar nor empty. – Pierre Feb 26 '18 at 11:30
  • 1
    @Pierre those are good questions, and ones that would require you to investigate: we can't see your data and we can't see all of your code; but: that doesn't change what I said - it is the *server* raising this error, not the client – Marc Gravell Feb 26 '18 at 11:36
0

Interesting! "No changes has been made to the code, and no new data has entered the queried table since then."
So the question why it doesn't work as the same way really have a many choice for you

  1. Your code just don't run into the problem date data before.
  2. Application server have something changed like date region/culture so it affect the application way of seeing date when communicate with DB server
  3. the 2. but from DB server e.g. Infra team upgrade/patch the DB server, the configuration is affect so this has problem with date format , update/patching Db cause the build-in functions too have upgraded behavior too!)
  4. The statement in double-quote is false.
  5. System.Data.SqlClient 's feature when process the query ? and so on...

I think finding the causes just from this little information is the very difficult task. From my little search ,you really should go diving into the query and data as the others tried to suggest. Conversion failed when converting date and/or time from character string while inserting datetime

user3682728
  • 467
  • 3
  • 7