|
|
Try this, it’s a cleaner approach and depending on your data types could have a different behaviour.
DATEDIFF(
YEAR,
ISNULL(d.DateofBirth, c.dob),
COALESCE(
CAST(NULLIF(c.datedeath, '00000000') AS DATE),
c.DateofAttendance,
GETDATE()
)
)
If that doesn’t help, confirm the exact data types of the columns you are referencing in this expression.
If that doesn’t help, divide an conquer your SP. As @JeroenMostert said; execute each part fo the SP in sequence until you have rigorously identified which part of it is actually generating the error.
For example, in the expression above, if it’s generating an error you could try Just the COALESCE() part, and see if that works or not…
COALESCE(
CAST(NULLIF(c.datedeath, '00000000') AS DATE),
c.DateofAttendance,
GETDATE()
)
Finally, for the love of all things good, consider amending your data structure so that dates are actually held as DATE and not VARCHAR or INT.
On Transact SQL language the Msg 245 Level 16 — Conversion failed when converting the varchar value means that you insert varchar values into an int column.
Msg 245 Level 16 Example:
We have the table TEST:
USE model;
GO
CREATE TABLE TEST(
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(10) NOT NULL );
GO
Invalid insert:
USE model;
GO
INSERT INTO TEST(id, name) VALUES ('abc', 'Olivia');
GO
| Message |
|---|
| Msg 245, Level 16, State 1, Line 1 |
| Conversion failed when converting the varchar value ‘abc’ to data type int. |
Correct insert:
USE model;
GO
INSERT INTO TEST(id, name) VALUES (1, 'Olivia');
GO
| Message |
|---|
| (1 row(s) affected) |
Other error messages:
- Conversion failed when converting date and/or time from character string
- Is not a defined system type
- Unknown object type used in a CREATE, DROP, or ALTER statement
- Cannot insert the value NULL into column
- Cannot insert explicit value for identity column in table
- The INSERT statement conflicted with the FOREIGN KEY constraint
- The DELETE statement conflicted with the REFERENCE constraint
SQL Server error Msg 245, Level 16 tells us that there was a problem when trying to convert a value to a specific data type.
You’ll get this error if you try to insert the wrong data type into a column.
To fix this issue, make sure the data type of the value you’re trying to insert, matches the column’s type.
Example of Problem Code
Here’s an example of code that results in this error.
INSERT INTO Orders(OrderId, OrderDate, OrderDesc)
VALUES ('2020-04-02', '2020-04-02', 'Dog food');Result:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '2020-04-02' to data type int.
In this case I tried to insert a date into the OrdersId column. We can assume by the error message that this column is an int column, but we should verify that.
If we look at the table definition, we can see the OrderId column’s type:
CREATE TABLE Orders (
OrderId int NOT NULL,
OrderDate date NOT NULL,
OrderDesc varchar(255) NOT NULL,
CONSTRAINT PKOrders PRIMARY KEY CLUSTERED(OrderId, OrderDate)
);As expected, the OrderId column is an int column.
Solution
To resolve this issue, we need to make sure that we’re inserting the correct value. We also need to ensure that the table definition is appropriate for the data that it needs to store. This will help enforce the data integrity of our database.
In our case, the column’s data type is correct. The problem was caused by accidentally trying to insert the wrong data.
Therefore, to fix the issue, we can change our INSERT statement to insert the correct data.
INSERT INTO Orders(OrderId, OrderDate, OrderDesc)
VALUES (1, '2020-04-02', 'Dog food');Result:
(1 row affected)
Success!
Table of Contents
SQL Server Error : 245 Details
SQL Server Error: 245
Severity: 16
Event Logged or not: No
Description:
Conversion failed when converting the %ls value ‘%.*ls’ to data type %ls.
Severity 16 Description:
Indicates general errors that can be corrected by the user.
SQL SERVER – Error: Msg 245 – Conversion failed when converting the varchar value ‘AllisWell’ to data type int
Solution for Resolving SQL Server Error 245
It’s critical that all return values be of the same data type when converting the data type. We’d wind up with conversion issues if we didn’t do this.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SQlAuthority' to data type int.
The column to which we are converting is an integer, but the value “AllisWell” is a string, so it can not be converted and thus the error SQL Server Error 245. Data Type Precedence is due to the datatype precedence rule. SQL Server is attempting to convert the data type String to Integer implicitly.
Make sure a integer values conversion possible values are input for any conversion functions. Otherwise this error message says that its not possible to convert the datatype.
Reading sql server error log location from SQL Query
Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.
USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go
The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html
SQL Server Error Code and solution summary
SQL Server Error: 245
Severity: 16
Event Logged or not: No
Description:
Conversion failed when converting the %ls value ‘%.*ls’ to data type %ls.
Make sure the input values which are tried for conversion to integer are not string values which will throw this error.