querying for dates by text

[Dirk_1234]

when I still used PL/SQL Developer in Version 11.x , I could write queries like:

select * from my_tab t where t.day = '01.01.2017';

Now with Version 12.x this doesn't work anymore, I get an error saying invalid month. I tried different literals like '2017-01-01' but no luck.

Is there any way to activate this helpful Feature in Version 12.x so I can write queries in this abbreviated form?

 

[Nicolas D.]

try the standard Oracle Date literal:

select * from my_tab t where t.day = DATE '2017-01-01';

Keyword DATE follow by quoted string format 'YYYY-MM-DD'

This work regardless of NLS language or date format, in PL/SQL Dev (any version), SQL*Plus, everywhere.

 

[Dirk_1234]

thanks, I didn't know about this way to do it. But I'm still very sad that the old and abbreviated way doesn't work anymore.

But one more question: How can I use a different date format? That used to work out of the box in the old PL/SQL-Developer version.

When I display dates, it all works fine, they get displayed in the german format. (Settings -> NLS Options -> Date is by default the german format for me). But the SQL Editor seems to only accept the YYYY-MM-DD format.

 

[Arky]

My suggestion is to never write the query like that (day='01.01.2017').

Always be at the safe side writing the code like this :

select * from my_tab t
where t.day = to_date('2017-12-23','YYYY-MM-DD') or to_date('01.02.2017', 'DD.MM.YYYY')
or whatever format you prefer.

If however I cannot convince you , you can run the following
SELECT *
FROM nls_database_parameters a
WHERE a.PARAMETER = 'NLS_DATE_FORMAT';

and then use the format returned at "where t.day = ..."

 

[Nicolas D.]

I agree with Arky,

be safe and always use either the default literal DATE 'YYYY-MM-DD' or TO_DATE to use any format you want.

Query the way you write it assume the default date format of the combination of the database and client settings, but can *very very* easily drive you to numerous problems if config change/run from another client with different config. This is a bad habit you must change.

I personally always use the literal for date as soon as I do not need timestamp. It is quick, fast, and safe.

 

[Dirk_1234]

while I understand your suggestion, to never use that syntax, it's just boiler plate code that I now have to write dozens of times per day without any necessity for it and always in the exact same way. Just because my PL/SQL-Developer was upgraded from 11 to 12.

I'm not talking about my productive code, that is handled via jdbc and correct. I'm just talking about the daily work as a developer where I'm manually on the database.

If I switch clients and the query I type there isn't working, then I'll rewrite it. There is no hazard.

The NLS_DATE_FORMAT in the database is 'DD-MON-RR'. I definitely won't be using that.

When I use SQLPlus, I can still use my preferred way of writing. And it's terribly sad to see that SQLPlus is more comfortable than PL/SQL Developer now.

How can I have the same comfort as in SQLPlus in PL/SQL-Developer? Is there any way? And why was PL/SQL-Developer made more uncomfortable with version 12?

 

[eetele]

Just alter the nls settings for your session to any format you are using in your selects and it should work. Either at the start of your scripts or at login.sql .
But writing date=[any_formatted_date_sting] is a bad pratice at all, blaming PL/SQL developer is an option but it wont get you nowhere now.

 

[Dirk_1234]

I still don't understand this. In my PL/SQL setting, I configured the date format to 'dd.MM.yyyy'. But I have to write ... = DATE '2017-08-29' in the 'yyyy-MM-dd' format.

When I use a different client other than PL/SQL Developer 12 (like sqlplus or PL/SQL Developer 11, etc.) then I can write dates in the 'dd.MM.yyyy' format, so it can't be an issue about my database setting, it must be the client.

Where can I configure the date format for PL/SQL Developer 12? The configuration under setting -> NLS Options -> Date is not working.

 

[Marco Kalter]

The preference applies to the date format as displayed in the user interface, such as the SQL Window result grid, Test Window variables, and so on.

To set a specific date format for your Oracle sessions you can use the NLS_DATE_FORMAT setting. It can be set in the Oracle Registry, as a Windows environment variable, or as a PL/SQL Developer parameter.

The PL/SQL Developer parameter can be set in the Params.ini file in the installation directory. Just add the following line:

NLS_DATE_FORMAT=DD.MM.YYYY

 

[William Robertson]

I still don't understand this. In my PL/SQL setting, I configured the date format to 'dd.MM.yyyy'. But I have to write ... = DATE '2017-08-29' in the 'yyyy-MM-dd' format.

An ANSI date literal takes only one format, 'YYYY-MM-DD', as documented. It will therefore always work as expected, regardless of NLS or any other desktop settings. That is the whole point of it. It's worth getting into the habit of always using this for date literals - anything else will one day do something unexpected.