Page History

...

• Date Formatting
  
  
  • To format a date in standard eye-readable format in your output, you may want to convert the date using the "TO_CHAR" function. Syntax is: TO_CHAR (table.field::date type, 'format that you want to see it in'). If you want to show the time components, the date has to be cast in the "timestamp" data type. 
    

    TO_CHAR (cl.loan_date::DATE,'mm/dd/yyyy') AS simple_date,

    TO_CHAR (cl.loan_date::TIMESTAMP,'mm/dd/yyyy hh:mi am') AS date_with_time_and_am_pm
    
    TO_CHAR (json_extract_path_text (ffa.data,'metadata','createdDate')::TIMESTAMP,'mm/dd/yyyy hh:mi am') as date_with_hours_and_minutes_am_pm
    

• • Note that the TO_CHAR function converts the date to TEXT (it will no longer function as a date)

• Extracting Date Parts

-- When you want the month number (1-12) or weekday number (1-7), use the "Extract" function OR the "date_part" function 

-- When you want the month name or weekday name, use the "to_char" function.

-- When you want the numerical day of the month (1-31), use the "date_part" function

date_part ('month', li.loan_date) as month_number_using_date_part

date_part ('day', li.loan_date) as day_of_month_number_using_date_part

date_part ('dow', li.loan_date) as day_of_week_number_using_date_part

to_char (li.loan_date, 'Mon') as month_name_using_tochar

to_char (li.loan_date, 'Dy') as day_name_using_tochar

extract (month FROM li.loan_date) as month_number_using_extract

extract (day FROM li.loan_date) as day_of_month_number_using_extract

extract (dow FROM li.loan_date) as day_of_week_number_using_extract

• Finding the elapsed time between two dates

...