Conversion failed when converting the varchar value to data type int. [SQLSTATE 22018] (Error 245)
up vote
0
down vote
favorite
I have sql job that is failing and it produce the above error. the job runs Stored Procedure so I tried to run the procedure so I can pin point the issue.. the SP surprisingly is not failing but at the same time not doing the job ( the SP should insert the data to another table which is not doing). I've looked through the SP thoroughly and I am thinking the below line might be the issue ??
I tried to add cast as varchar at the end and it didn't work. the below line should calculate the client age and insert it into 'Age' column data type numeric(18,6)..
columns type:
d.DateofBirth Date
c.dob DateTime
c.datedeath varchar(50)
c.DateofAttendance DateTime
Can any one help, please???
CAST(DATEDIFF(DAY, ISNULL(d.DateofBirth, c.dob),ISNULL(CAST(CASE WHEN
c.datedeath = '00000000' THEN NULL ELSE c.DateDeath END AS DATE),
ISNULL(c.DateofAttendance,GETDATE()))) / 365.25 AS INT) AS age
sql tsql stored-procedures
asked Nov 8 at 11:29
Ayman
32
|
show 2 more comments
up vote
0
down vote
favorite
I have sql job that is failing and it produce the above error. the job runs Stored Procedure so I tried to run the procedure so I can pin point the issue.. the SP surprisingly is not failing but at the same time not doing the job ( the SP should insert the data to another table which is not doing). I've looked through the SP thoroughly and I am thinking the below line might be the issue ??
I tried to add cast as varchar at the end and it didn't work. the below line should calculate the client age and insert it into 'Age' column data type numeric(18,6)..
columns type:
d.DateofBirth Date
c.dob DateTime
c.datedeath varchar(50)
c.DateofAttendance DateTime
Can any one help, please???
CAST(DATEDIFF(DAY, ISNULL(d.DateofBirth, c.dob),ISNULL(CAST(CASE WHEN
c.datedeath = '00000000' THEN NULL ELSE c.DateDeath END AS DATE),
ISNULL(c.DateofAttendance,GETDATE()))) / 365.25 AS INT) AS age
sql tsql stored-procedures
asked Nov 8 at 11:29
Ayman
32
This line is not the issue. At no point is any conversion betweenVARCHARandINTinvolved, except possibly if00000000was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing aVARCHARcolumn with anINTvalue instead of a string, which implicitly converts the column values toINT, rather than theINTto aVARCHAR.
– Jeroen Mostert
Nov 8 at 11:35
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. Ifd.DateofBirthis anINTandc.dobis aVARCHAR()thenISNULL(d.DateofBirth, c.dob)could be a problem.... After all,c.datedeathis certainly a string, so anything is possible...
– MatBailie
Nov 8 at 11:38
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
1
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42
|
show 2 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have sql job that is failing and it produce the above error. the job runs Stored Procedure so I tried to run the procedure so I can pin point the issue.. the SP surprisingly is not failing but at the same time not doing the job ( the SP should insert the data to another table which is not doing). I've looked through the SP thoroughly and I am thinking the below line might be the issue ??
I tried to add cast as varchar at the end and it didn't work. the below line should calculate the client age and insert it into 'Age' column data type numeric(18,6)..
columns type:
d.DateofBirth Date
c.dob DateTime
c.datedeath varchar(50)
c.DateofAttendance DateTime
Can any one help, please???
CAST(DATEDIFF(DAY, ISNULL(d.DateofBirth, c.dob),ISNULL(CAST(CASE WHEN
c.datedeath = '00000000' THEN NULL ELSE c.DateDeath END AS DATE),
ISNULL(c.DateofAttendance,GETDATE()))) / 365.25 AS INT) AS age
sql tsql stored-procedures
asked Nov 8 at 11:29
Ayman
32
I have sql job that is failing and it produce the above error. the job runs Stored Procedure so I tried to run the procedure so I can pin point the issue.. the SP surprisingly is not failing but at the same time not doing the job ( the SP should insert the data to another table which is not doing). I've looked through the SP thoroughly and I am thinking the below line might be the issue ??
I tried to add cast as varchar at the end and it didn't work. the below line should calculate the client age and insert it into 'Age' column data type numeric(18,6)..
columns type:
d.DateofBirth Date
c.dob DateTime
c.datedeath varchar(50)
c.DateofAttendance DateTime
Can any one help, please???
CAST(DATEDIFF(DAY, ISNULL(d.DateofBirth, c.dob),ISNULL(CAST(CASE WHEN
c.datedeath = '00000000' THEN NULL ELSE c.DateDeath END AS DATE),
ISNULL(c.DateofAttendance,GETDATE()))) / 365.25 AS INT) AS age
sql tsql stored-procedures
sql tsql stored-procedures
asked Nov 8 at 11:29
Ayman
32
asked Nov 8 at 11:29
Ayman
32
edited Nov 8 at 12:00
asked Nov 8 at 11:29
Ayman
32
asked Nov 8 at 11:29
Ayman
32
asked Nov 8 at 11:29
Ayman
32
32
This line is not the issue. At no point is any conversion betweenVARCHARandINTinvolved, except possibly if00000000was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing aVARCHARcolumn with anINTvalue instead of a string, which implicitly converts the column values toINT, rather than theINTto aVARCHAR.
– Jeroen Mostert
Nov 8 at 11:35
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. Ifd.DateofBirthis anINTandc.dobis aVARCHAR()thenISNULL(d.DateofBirth, c.dob)could be a problem.... After all,c.datedeathis certainly a string, so anything is possible...
– MatBailie
Nov 8 at 11:38
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
1
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42
|
show 2 more comments
This line is not the issue. At no point is any conversion betweenVARCHARandINTinvolved, except possibly if00000000was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing aVARCHARcolumn with anINTvalue instead of a string, which implicitly converts the column values toINT, rather than theINTto aVARCHAR.
– Jeroen Mostert
Nov 8 at 11:35
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. Ifd.DateofBirthis anINTandc.dobis aVARCHAR()thenISNULL(d.DateofBirth, c.dob)could be a problem.... After all,c.datedeathis certainly a string, so anything is possible...
– MatBailie
Nov 8 at 11:38
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
1
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42
This line is not the issue. At no point is any conversion between
VARCHAR and INT involved, except possibly if 00000000 was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing a VARCHAR column with an INT value instead of a string, which implicitly converts the column values to INT, rather than the INT to a VARCHAR.– Jeroen Mostert
Nov 8 at 11:35
This line is not the issue. At no point is any conversion between
VARCHAR and INT involved, except possibly if 00000000 was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing a VARCHAR column with an INT value instead of a string, which implicitly converts the column values to INT, rather than the INT to a VARCHAR.– Jeroen Mostert
Nov 8 at 11:35
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. If
d.DateofBirth is an INT and c.dob is a VARCHAR() then ISNULL(d.DateofBirth, c.dob) could be a problem.... After all, c.datedeath is certainly a string, so anything is possible...– MatBailie
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. If
d.DateofBirth is an INT and c.dob is a VARCHAR() then ISNULL(d.DateofBirth, c.dob) could be a problem.... After all, c.datedeath is certainly a string, so anything is possible...– MatBailie
Nov 8 at 11:38
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
1
1
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42
|
show 2 more comments
1 Answer
1
active
oldest
votes
up vote
-1
down vote
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.
answered Nov 8 at 11:50
MatBailie
58.1k1373110
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
-1
down vote
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.
answered Nov 8 at 11:50
MatBailie
58.1k1373110
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
add a comment |
up vote
-1
down vote
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.
answered Nov 8 at 11:50
MatBailie
58.1k1373110
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
add a comment |
up vote
-1
down vote
up vote
-1
down vote
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.
answered Nov 8 at 11:50
MatBailie
58.1k1373110
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.
answered Nov 8 at 11:50
MatBailie
58.1k1373110
answered Nov 8 at 11:50
MatBailie
58.1k1373110
answered Nov 8 at 11:50
MatBailie
58.1k1373110
answered Nov 8 at 11:50
MatBailie
58.1k1373110
58.1k1373110
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
add a comment |
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
I followed your example and it didn't work then i tried the second part for testing and i got this error: Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
– Ayman
Nov 8 at 12:27
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
You're trying to do too much. The error message appears to be that you're trying to INSERT the result in to the destination. I'm suggesting you just run the SELECT statement, separately from the rest of the Stored Procedure. If you keep trying to run the whole SP to debug this, you'll be here forever.
– MatBailie
Nov 8 at 12:38
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
alright .. thank you. I will break down my SP
– Ayman
Nov 8 at 13:07
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206864%2fconversion-failed-when-converting-the-varchar-value-to-data-type-int-sqlstate%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
This line is not the issue. At no point is any conversion between
VARCHARandINTinvolved, except possibly if00000000was converted, but that conversion would always succeed. Look further. Common culprits of this error is inadvertently comparing aVARCHARcolumn with anINTvalue instead of a string, which implicitly converts the column values toINT, rather than theINTto aVARCHAR.– Jeroen Mostert
Nov 8 at 11:35
thank you.. how can I debug the SP to find the line with the issue if the SP is succeeding ?? any idea?
– Ayman
Nov 8 at 11:38
@JeroenMostert - That depends on the datatypes of the columns. If
d.DateofBirthis anINTandc.dobis aVARCHAR()thenISNULL(d.DateofBirth, c.dob)could be a problem.... After all,c.datedeathis certainly a string, so anything is possible...– MatBailie
Nov 8 at 11:38
Please confirm the data types of all of the columns referenced by this expression.
– MatBailie
Nov 8 at 11:39
1
@Ayman: take the stored procedure definition, convert the parameters to variables, execute it manually. If the line number doesn't give you enough clues, divide and conquer by selectively eliminating statements until you've found the offending one. Unfortunately T-SQL's help in debugging these things is nonexistent.
– Jeroen Mostert
Nov 8 at 11:42