How to convert string date into date format in sqlite

Question

I need to find the average age but my DOB is stored in the format Month,day,year(eg:-May,2,1986) in my sqlite database .

How can i convert from May,2,1986 into date format to find the age?

Regards Nikil

Answer 1

This solution only considers the year, not the month + day (which you can add to it).

SELECT
   DATE('now') -
   CAST(
      SUBSTR(birth_date,-4) || '-' ||
      CASE SUBSTR(birth_date,0,LENGTH(birth_date)-8)
         WHEN 'January' THEN 1
         WHEN 'February' THEN 2
         WHEN 'March' THEN 3
         WHEN 'April' THEN 4
         WHEN 'May' THEN 5
         WHEN 'June' THEN 6
         WHEN 'July' THEN 7
         WHEN 'August' THEN 8
         WHEN 'September' THEN 9
         WHEN 'October' THEN 10
         WHEN 'November' THEN 11
         WHEN 'December' THEN 12
      END || '-' ||
      SUBSTR(birth_date, 6,1)
   AS DATE)
FROM foo;

This will give you 26.0.

Answer 2

try this declare @dob varchar(200) set @dob = 'May,2,1986' select convert(datetime, @dob)

if this doesnt work you will need to substring the year month and day and then assign them to a variable together and substract with current date(getdate()) to get your age.