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)

Answer 1

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)