/* Date functions in SQLite */

/* Obtain the current date/time.  GMT and localtime ------------- */
SELECT DATETIME(), DATETIME('now','localtime');      


/* get the year, month and day ----------------------------------- */
SELECT STRFTIME('%Y %m %d', DATETIME());     


/* Obtain the hour the minute and the second -------------------- */
SELECT STRFTIME('%H %M %S', DATETIME());      


/* get the day name --------------------------------- */
SELECT  
    CASE  STRFTIME( '%w', datetime('now','localtime'))
        WHEN '0' then 'Sunday'
        WHEN '1' then 'Monday'
        WHEN '2' then 'Tuesday'
        WHEN '3' then 'Wednesday'
        WHEN '4' then 'Thursday'
        WHEN '5' then 'Friday'
        WHEN '6' then 'Saturday' 
    END as day_of_week;  


/* get the month name --------------------------------- */
SELECT  
    CASE  STRFTIME( '%m', datetime('now','localtime'))
        WHEN '01' then 'January'
        WHEN '02' then 'February'
        WHEN '03' then 'March'
        WHEN '04' then 'April'
        WHEN '05' then 'May'
        WHEN '06' then 'June'
        WHEN '07' then 'July'
        WHEN '08' then 'August'
        WHEN '09' then 'September'
        WHEN '10' then 'October'
        WHEN '11' then 'November'
        WHEN '12' then 'December'
     END as month_name;  
	 
	 
/* obtain Julian day (need to subtract the Julian day for first of the year) ------ */
SELECT JULIANDAY(DATETIME('now','localtime')) - JULIANDAY('2024-01-01');                             


/* Obtain the last day of the month ----------------------------- */
SELECT DATE('2024-02-15', 'start of month', '+1 month', '-1 day');


/* Add 20 days to the current day ------------------------------- */
SELECT DATETIME('now', 'localtime', '+20 day'); 


/* Subtract 150 minutes, a.k.a. 2 & 1/2 hours ------------------- */
SELECT DATETIME('now', 'localtime', '-2.5 hour'); 

                        
/* Compute the number of days in between 2 dates ----------------- */
SELECT JULIANDAY(DATETIME('now')) - JULIANDAY('1776-07-04');


/* Print the date in a special format --------------------------- */
SELECT STRFTIME('%m/%d/%Y @ %H:%M:%S', DATETIME('now','localtime'));      /* notice spacing & punctuation */


/*--------------------------------------------------------------------------------*/