how to get date in dd/mm/yyyy format in sql query

Question

Presently my query returning date as 05-04-17 06:31:17.881698000 PM.

But I want to return like 20/10/201. I have reffered this link But I am unable to intigrate this query in my query as they are taking columns individually.

select * from test_Table where reg_id = ?

please choose your tags accordingly, I was about to post a mysql query... — nafas, Apr 05 '17 at 13:21
Possible duplicate of [Oracle's default date format is YYYY-MM-DD, WHY?](http://stackoverflow.com/questions/1837974/oracles-default-date-format-is-yyyy-mm-dd-why) — nafas, Apr 05 '17 at 13:23
You should be able to find answer by just googling it. In any case, look at to_char oracle function: [Oracle documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm). Please note, you'll have to rewrite your query. — Mike Kachaluba, Apr 05 '17 at 13:23

score 3 · Accepted Answer · answered Apr 05 '17 at 13:26

3

You can simply give in the format as you like DD/MM/YYYY

select to_char(yourdate,'DD/MM/YYYY' ) from test_Table where reg_id = ?

answered Apr 05 '17 at 13:26

Rams

2,129
1
12
19

score 0 · Answer 2 · answered Apr 05 '17 at 13:48

If you insist on using your query as-is (in general, using SELECT * is not a best practice), you can change the date format for your session with the following query:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

You can then run your query and the date should be returned in the format you want. Note that you'll have to do this with every session you start where you want the date in this format.

score -2 · Answer 3 · answered Apr 05 '17 at 13:23

-2

You can use the below SQL Query to Query for the Date as the format you are asking for.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

You can query it directly or you can use it somewhere as variable to store it and then use it on the query you want.

Hope this help you.

answered Apr 05 '17 at 13:23

Mamoon Rasheed

71
6

2

This is not Oracle syntax – Wernfried Domscheit Apr 05 '17 at 13:39

how to get date in dd/mm/yyyy format in sql query

3 Answers3