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

Cannot you use to_char? to_char(order_date, 'YYYY MM/DD') – Avani Jan 09 '17 at 17:31 — Avani, Jan 09 '17 at 17:31
@Avani `to_char` is Oracle specific. – Mark Rotteveel Jan 10 '17 at 10:07 — Mark Rotteveel, Jan 10 '17 at 10:07

score 4 · Accepted Answer · edited Feb 27 '21 at 08:06

4

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')

edited Feb 27 '21 at 08:06

Mark Rotteveel

100,966
191
140
197

answered Jan 09 '17 at 17:29

ain

22,394
3
54
74

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

1 Answers1