How to get date format as "yyyy mm/dd"?

Question

I am trying to output a time stamp field from view in this format yyyy mm/dd so I tried like this:

extract(year from mytable.order_date)||' '||extract(month from mytable.order_date)||'/'||extract(day from mytable.order_date),

however there is no padding zeros so how I add them ?

using Firebird 3.0

Answer 1

You could use the lpad function to pad with 0, ie

extract(year from mytable.order_date) ||' '||
   lpad(extract(month from mytable.order_date), 2, '0')||'/'||
   lpad(extract(day from mytable.order_date), 2, '0')