I have the following SQL code to create a view called v_students and I want to use the DATEDIFF() function to find the difference between the current date and the registration_date and then place those values in a new column called DaysSinceRegistration. I am running into an issue where there is a syntax error on the DaysSinceRegistration column name. Any suggestions, not sure what I am doing wrong?
x
CREATE VIEW v_students AS
SELECT
registration_date,
student_id,
salutation,
first_name,
last_name,
street_address,
zip,
phone,
employer,
fullname,
DATEDIFF(day, registration_date, CURDATE()) as 'DaysSinceRegistration'
FROM
student
Advertisement
Answer
That’s because DATEDIFF
isn’t Oracle (while PL/SQL, tag you used, is).
Difference in days is get by simply subtracting two DATE
datatype values. Round them to see a nicer value. So:
round(sysdate - registration_date) as dayssinceregistration