How to format date in hana

Question

I need to format date in my hana sql, but I don't know how to. Something like this:

 SELECT
 DATE_FORMAT(DATAS,'%Y-%m') as Dat 
,sum(SALES_VALUE) as Venda
,sum(SALES_QTY) as Qtd
,sum(SALES_VALUE) / sum(SALES_QTY) as  Preco
FROM looqdata.data_store_sales as s
inner join
looqdata.data_store_cad as c
on s.STORE_CODE = c.STORE_CODE
where 1=1
and DATAS between '2016-01-04' and '2016-02-10'
and s.STORE_CODE in  (1,2) 
group by DATE_FORMAT(DATAS,'%Y-%m') 

Answer 1

Date-data types in SAP HANA, just as in most other DBMS, don't have a specific formatting associated with them. The formatting is part of the output rendering process and locale dependent setting usually come into play here. You may check my blog on this.

If you want to force a specific format and accept that the data type becomes a string data type, then using conversion functions like TO_VARCHAR() reference docu link can be used.

E.g.

SELECT 
      TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to varchar" 
FROM DUMMY;

Converts a date string from format YYYY-MM-DD to date format YYYY/MM/DD.