So far, our MORT_FINAL_2 table contains 16 columns: 6 from the VISIT table, 2 from the PATIENT table, and 8 from the VITALS table (you can verify this by using the SELECT * FROM MORT_FINAL_2; command). In this query set, we transform one of these variables, the birthdate variable, into a usable form using date manipulation: we calculate the patients' age.

As we said earlier in Query Set #2a, dates are stored in SQLite as TEXT variable types and are in the YYYY-MM-DD format. Calculating the age takes two calls to the julianday() function. In SQLite, the julianday() function takes a date in YYYY-MM-DD as input and returns the number of days since November 24th, 4714 BC 12:00 PM as a float. By itself, it may not seem very useful, but when used in combination with another julianday() call and a subtraction sign, it allows you to find the number of days that have elapsed between two dates. Following that, we calculate the Julian day difference between the visit date and the birthdate and divide the result by 365.25 to give us the patient ages in years. We also apply the ROUND() function to this result and round to two places after the decimal point (which is what the 2 indicates before the final parentheses are closed):

sqlite> ALTER TABLE MORT_FINAL_2 ADD COLUMN Age_years REAL;

sqlite> UPDATE MORT_FINAL_2 SET Age_years = 
ROUND((julianday(Visit_date) - julianday(Bdate)) / 365.25,2);