CODEWITHSUNDEEP
sqloracle-databasedate
nutella_eater
nutella_eater

Reputation: 3572

Date in right format

guys. I hava the query,which counts the number of some events per one day. But it returns date in wrong format. 

select trunc(action_dt) as action_dt, count(*) as cnt
from stat.jurnal_orders
where action_dt between TO_DATE('17.05.12','DD/MM/YY') AND TO_DATE('12.11.13','DD/MM/YY')
group by trunc(action_dt)
order by action_dt asc

In database I have my date in DD.MM.YYYY but I want YYYY/MM/DD Can somebody help me with that query?

Upvotes: 0

Views: 116

Answers (4)

Thomas Wood
Thomas Wood

Reputation: 819

SELECT REPLACE(CONVERT(VARCHAR(10), action_dt, 102), '.', '/')

http://msdn.microsoft.com/en-us/library/ms187928.aspx

EDIT: This is for T-SQL not Oracle. Misread the question.

Upvotes: -1

James Xabregas
James Xabregas

Reputation: 766

To convert dates into a different format you can use the Oracle TO_CHAR function. http://www.techonthenet.com/oracle/functions/to_char.php

For your example, assuming you want to TRUNC the date first, you can use it like so:

SELECT TO_CHAR(TRUNC(action_dt), 'YYYY/MM/DD') as action_dt
FROM some_table

Upvotes: 1

haki
haki

Reputation: 9759

you have two options

First - explicitly set the format you want in the query using TO_CHAR

select to_char(trunc(action_dt),'yyyy/mm/dd') as action_dt, count(*) as cnt
from stat.jurnal_orders
where action_dt between TO_DATE('17.05.12','DD/MM/YY') AND TO_DATE('12.11.13','DD/MM/YY')
group by to_char(trunc(action_dt),'yyyy/mm/dd')
order by action_dt asc

Second, set the nls_date_format at session level.

alter session set nls_date_format = 'yyyy/mm/dd';

and you query 

select trunc(action_dt) as action_dt, count(*) as cnt
    from stat.jurnal_orders
    where action_dt between TO_DATE('2012/05/17') AND TO_DATE('2013/12/11')
    group by trunc(action_dt)
    order by action_dt asc

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please try TO_CHAR:

select TO_CHAR (sysdate, 'YYYY/MM/DD') from dual;

Upvotes: 0

Related Questions