CODEWITHSUNDEEP
sqloracle-databasedateformat
Bylox
Bylox

Reputation: 28

Bad date format Oracle SQL

I'm facing an issue with date format. I need the row with the max(date1) I don't think that my query is incorrect :

SELECT column1, 
    column2, 
    date1
FROM table
WHERE date1 = (SELECT MAX(date1) FROM table);

When i use this query in dev environment with TOAD, i'm receiving that result :

column1;column2;date1

aaaaa;bbbbb;19/09/2014 14:13:21

But, when i ask our infrastructure to make this query in production, they returned me that result :

column1;column2;date1

ccccc;dddddd;14/09/26

The date isn't in good format... What can i do the receive the good date ? Does i have to specified the format in my query ? Or does the infra team messed up somethings while giving me the result ?

Thanks in advance for your help

Upvotes: 1

Views: 566

Answers (1)

Mureinik
Mureinik

Reputation: 311103

date1 is a date column - it doesn't have any intrinsic format - that's up to the client and the environment to determine when printing it. If you want to control the format, you need to do so explicitly with the to_char function:

SELECT column1, 
       column2, 
       TO_CHAR(date1, 'DD/MM/YYYY hh24:mi:ss')
FROM   table
WHERE  date1 = (SELECT MAX(date1) FROM table);

Upvotes: 3

Related Questions