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.

Answer 2

As Lars explained you can use TO_VARCHAR() function for converting a date expression into a desired format

If you want to convert a date which is already in a column of data type DATE, you don't need an additional TO_DATE() conversion In such a case, you can directly use TO_VARCHAR() for date format conversion in SQLScript

Please check following SQLScript code on your development SAP HANA database

create column table ProductValue (
Product      varchar(10),
Date     date,
Value  int
);
insert into ProductValue values ('P1','20171001',10);

select *, TO_VARCHAR(Date,'DD.MM.YY')  from ProductValue;
select *, TO_VARCHAR(Date,'DD.MM.YYYY')  from ProductValue;
select *, TO_VARCHAR(Date,'YYYY-MM')  from ProductValue;
select *, TO_VARCHAR(Date,'YYYY-MM-DD')  from ProductValue;
select *, TO_VARCHAR(Date,'YYYY/MM/DD')  from ProductValue;
select *, TO_VARCHAR(TO_DATE(Date),'YYYY/MM/DD')  from ProductValue;