DATETIME format set to YYYY:MM:DD in SQL but insert query runs on DD:MM:YYYY in SQL Developer

Question

I have DATETIME set as DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm"); in my getDateTime() method in my DTO class and this method returns a date with time. This is further used to create a CSV(Excel) file but when I run INSERT/UPDATE query in SQL developer, it throws an error if I use the format as YYYY:MM:DD HH:MM'. ForDD:MM:YYYY HH:MMit runs successfully. Also in some cases while creating CSV, the date field gets missed. I have also checked DB table and the format for DATE is set as DATE in there. Can anyone suggest the possible reason for

Error in SQL Dev while Updating/Inserting date as yyyy-MM-dd HH:mm
What the DATE field's format should be in SQL if my code has format as above. Or DATE should work just fine.

We are using Oracle as RDBMS. For running queries we use SQL Developer.

We are using Oracle as RDBMS. For running queries we use SQL Developer. — Nikhil Arora, Mar 25 '18 at 19:50
1) show us your update/insert statements and error message(s). 2) DATE format is just fine if you don't need the time part — jose_bacoy, Mar 25 '18 at 19:58
You use `yyyy-MM-dd HH:mm`, which says YEAR-MONTH-DAY HOURS:**MONTH** (month? Again?) >>> should be "mi", not "mm". — Littlefoot, Mar 25 '18 at 20:01
Just tag your questions with **the actual database product** you're using. Applying multiple irrelevant tags just confuses people and makes it less likely you'll get a helpful. timely answer. — APC, Mar 25 '18 at 22:31

score 0 · Answer 1 · answered Mar 26 '18 at 06:35

0

Take a look in your Tools => Preferences => Databases => NLS section in SQL Developer. It nominates the default date format (amongst other things) that the tool will expect. You can alter this to suit your needs.

answered Mar 26 '18 at 06:35

Connor McDonald

10,418
1
11
16

score 0 · Answer 2 · answered Mar 26 '18 at 09:45

There are a lot of misconceptions in the question:

I have DATETIME

Oracle does not have a DATETIME data type - it has DATE or TIMESTAMP both of which have a time component.

set as DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");

A DATE (or TIMESTAMP) column does not have a format; Oracle stores DATE data types as 7-bytes (and similar for TIMESTAMP data types) and it is not until it is passed to a client program (i.e. SQL/Plus, SQL Developer, Toad, Java, Python, etc) and that client program formats it according to whatever rules it has that the date gets a format.

The default string format for dates in SQL/Plus or SQL Developer is set by the NLS_DATE_FORMAT session parameter. Other clients will typically have parameters that you can set for the default date format (if they don't also use the NLS settings). However, beware that the NLS_DATE_FORMAT is a session parameter so it belongs to the user's session and multiple users can each have a different value for the parameter corresponding to how they have set it.

If you have overridden the default date format settings in the SQL Developer IDE then you can change it in "Tools" > "Preferences" > "Database" > "NLS".

For DD:MM:YYYY HH:MM it runs successfully.

The format model MM (and mm as it is not case sensitive) is for month and HH is a 12-hour clock so you are specifying:

day:month:year hours-on-12-hour-clock:month

If you want to give a date a specific format then you will need to convert it to a string:

SELECT TO_CHAR( SYSDATE, 'MM/DD/YYYY HH24:MI' )
FROM   DUAL;

or if you want to convert a string to a date then use:

SELECT TO_DATE( your_string, 'MM/DD/YYYY HH24:MI' )
FROM   DUAL;

Also in some cases while creating CSV, the date field gets missed.

It probably is not missed - it is probably just NULL and gets formatted as an empty string.

DATETIME format set to YYYY:MM:DD in SQL but insert query runs on DD:MM:YYYY in SQL Developer

2 Answers2