SQL Convert varchar to int

Question

I was trying to display the number of days between two dates(column) in my database. The output display was in the varchar data type. How should I convert it into int data type? Following is the error message that pops up to me:

Msg 245, Level 16, State 1, Line 42

Conversion failed when converting the varchar value 'Overdue Day(s)' to data type int.

I have tried using both CAST and CONVERT syntax, but neither of them worked.

SELECT DVD_mem_id AS 'Member ID',
       DVD_mem_name AS 'Member Name',
       DVD_movie_id AS 'Movie ID',
       DVD_movie_title AS 'Movie Title',
       DVD_borrowing_date AS 'Date of Borrow',
       DVD_date_due AS 'Date Due',
       CAST('Overdue Day(s)' AS int),
       DATEDIFF(DAY, DVD_borrowing_date, DVD_date_due) -3 AS 'Overdue Day(s)',
       'Overdue Day(s)' * 2 AS 'Fine Incurred'
FROM DVD_T
WHERE (DVD_mem_id IS NOT NULL) AND
      (DVD_mem_name IS NOT NULL) AND
      (DVD_borrowing_date IS NOT NULL) AND
      (DVD_date_due) IS NOT NULL AND
      ('Overdue Day(s)' > 0)

`'Overdue Day(s)'` is a string literal, which can't be casted to integer. — jarlh, Jun 27 '19 at 08:15
You can't reference a column alias from the same select in the select list, or in the where clause. — jarlh, Jun 27 '19 at 08:17
Possible duplicate of [Conversion failed when converting the varchar value 'simple, ' to data type int](https://stackoverflow.com/questions/21984110/conversion-failed-when-converting-the-varchar-value-simple-to-data-type-int) — Amira Bedhiafi, Jun 27 '19 at 08:32

score 1 · Accepted Answer · answered Jun 27 '19 at 08:16

I think what you need is this, as @jarlh said 'Overdue Day(s)' is a string where you cannot do any math operations.

SELECT DVD_mem_id AS 'Member ID',
       DVD_mem_name AS 'Member Name',
       DVD_movie_id AS 'Movie ID',
       DVD_movie_title AS 'Movie Title',
       DVD_borrowing_date AS 'Date of Borrow',
       DVD_date_due AS 'Date Due',
       CAST((DATEDIFF(DAY, DVD_borrowing_date, DVD_date_due) -3) AS int),
       DATEDIFF(DAY, DVD_borrowing_date, DVD_date_due) -3 AS 'Overdue Day(s)',
       (DATEDIFF(DAY, DVD_borrowing_date, DVD_date_due) -3) * 2 AS 'Fine Incurred'
FROM DVD_T
WHERE (DVD_mem_id IS NOT NULL) AND
      (DVD_mem_name IS NOT NULL) AND
      (DVD_borrowing_date IS NOT NULL) AND
      (DVD_date_due) IS NOT NULL AND
      (DATEDIFF(DAY, DVD_borrowing_date, DVD_date_due) -3 > 0)

Thanks a lot, you solved my problem, really appreciated ^v^ – Unknown66 Jun 27 '19 at 08:33 — Unknown66, Jun 27 '19 at 08:33

SQL Convert varchar to int

1 Answers1