Hi , My Toad update at version 12.12 them don’t execute querys like :
select * from my_table where campo_fecha >= to_date(‘01/10/2017’) ( first query )
now nedd complete like this : select * from my_table where campo_fecha >= to_date(‘01/10/2017’,‘dd/mm/rrrr’) ( second query )
How config TOAD for support first query ?
Thanks.
If I understand you correctly, put the to_date (to_char) on the column and the preferred output in the result.
select * from my_table where **to_char(campo_fecha,'dd/mm/rrrr' ** >= '01/10/2017' ;
On Tue, Oct 17, 2017 at 5:25 PM, lhernandez_75862 bounce-lhernandez_75862@toadworld.com wrote:
Thread created by lhernandez_75862
Hi , My Toad update at version 12.12 them don't execute querys like :
select * from my_table where campo_fecha >= to_date('01/10/2017') ( first query )
now nedd complete like this : select * from my_table where campo_fecha >= to_date('01/10/2017','dd/mm/rrrr') ( second query )
How config TOAD for support first query ?
Thanks.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
--
Gene L. Bradley Jr.
Systems Analyst
Office of Information Technology
Jackson State University
1400 J R Lynch Street
P.O. Box 17750
Jackson, MS 39217
ph 601.979.1042
fax 601.371.9146
email gbradley@jsums.edu
*In God we trust; all others bring data. * ~W.E. Deming
CONFIDENTIALITY STATEMENT
This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.
I understand, but with previous version of TOAD i put : select * from my_table where campo_fecha >= to_date('01/10/2017').
Nothing more and showme results. Without more formats.
Thanks.
--
Luis E. Hernández Manzanares
Soporte Sistemas de Gestión
**Sociedad Suizo Peruana ****de ****Embutidos S.A. **
SUPEMSA
Comunidad Industrial 240 - Chorrillos
Central: (51) 1 336-7000 Anexo: 1248
Celular: (51) 998 106 098
Atención de Soporte Técnico y Sistemas y Procedimientos de Sistemas
2017-10-17 17:35 GMT-05:00 gene.l.bradley bounce-genelbradley@toadworld.com:
Reply by gene.l.bradley
If I understand you correctly, put the to_date (to_char) on the column and the preferred output in the result.
select * from my_table where **to_char(campo_fecha,'dd/mm/rrrr' ** >= '01/10/2017' ;
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
Aviso de Confidencialidad y Protección de Datos Personales:
Este correo electrónico y/o el material adjunto es para uso exclusivo de la persona o entidad a la que expresamente se le ha enviado, y puede contener información confidencial y/o material privilegiado y/o protegido por la Ley de Protección de Datos Personales. Si usted es el destinatario legítimo del mismo, adicionalmente le indicamos que toda la información que nos brinde y que se considere como “datos personales” y/o “datos sensibles” según lo dispuesto en la Ley de Protección de Datos Personales, serán tratados de manera reservada y confidencial conforme lo establece la referida Ley y nuestro marco constitucional (Dar “click” para leer condiciones y “ACEPTAR” o “NO ACEPTAR” las mismas previamente leídas). Si usted no es el destinatario legítimo del mismo, por favor repórtelo inmediatamente al remitente del correo y bórrelo. Cualquier revisión, retransmisión, difusión o cualquier otro uso de este correo, por personas o entidades distintas a las del destinatario legítimo, queda expresamente prohibido. Este correo electrónico no pretende ni debe ser considerado como constitutivo de ninguna relación legal, contractual o de otra índole similar, en consecuencia, no genera obligación alguna a cargo de su emisor o su representada. En tal sentido, nada de lo señalado en esta comunicación o en sus anexos podrá ser interpretado como una recomendación sobre los riesgos o ventajas económicas, legales, contables o tributarias, o sobre las consecuencias de realizar o no determinada transacción. Este correo electrónico y/o el material adjunto deben contener información de carácter institucional, vinculada a las labores del remitente, en caso no tenga(n) dicha condición, el mismo es de exclusiva responsabilidad del remitente, sin responsabilidad alguna para Sociedad Suizo Peruana de Embutidos S.A. - SUPEMSA”.
what I s data type of Campo_fecha
On Oct 17, 2017 5:50 PM, "lhernandez_75862" bounce-lhernandez_75862@toadworld.com wrote:
Reply by lhernandez_75862
I understand, but with previous version of TOAD i put : select * from my_table where campo_fecha >= to_date('01/10/2017').
Nothing more and showme results. Without more formats.
Thanks.
--
Luis E. Hernández Manzanares
Soporte Sistemas de Gestión
**Sociedad Suizo Peruana ****de ****Embutidos S.A. **
SUPEMSA
Central: (51) 1 336-7000 Anexo: 1248
Celular: (51) 998 106 098
Atención de Soporte Técnico y Sistemas y Procedimientos de Sistemas
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
Is date.
Enviado desde mi Motorola g4 Play
El 17 oct. 2017 22:10, "gene.l.bradley" bounce-genelbradley@toadworld.com escribió:
Reply by gene.l.bradley
what I s data type of Campo_fecha
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
Aviso de Confidencialidad y Protección de Datos Personales:
Este correo electrónico y/o el material adjunto es para uso exclusivo de la persona o entidad a la que expresamente se le ha enviado, y puede contener información confidencial y/o material privilegiado y/o protegido por la Ley de Protección de Datos Personales. Si usted es el destinatario legítimo del mismo, adicionalmente le indicamos que toda la información que nos brinde y que se considere como “datos personales” y/o “datos sensibles” según lo dispuesto en la Ley de Protección de Datos Personales, serán tratados de manera reservada y confidencial conforme lo establece la referida Ley y nuestro marco constitucional (Dar “click” para leer condiciones y “ACEPTAR” o “NO ACEPTAR” las mismas previamente leídas). Si usted no es el destinatario legítimo del mismo, por favor repórtelo inmediatamente al remitente del correo y bórrelo. Cualquier revisión, retransmisión, difusión o cualquier otro uso de este correo, por personas o entidades distintas a las del destinatario legítimo, queda expresamente prohibido. Este correo electrónico no pretende ni debe ser considerado como constitutivo de ninguna relación legal, contractual o de otra índole similar, en consecuencia, no genera obligación alguna a cargo de su emisor o su representada. En tal sentido, nada de lo señalado en esta comunicación o en sus anexos podrá ser interpretado como una recomendación sobre los riesgos o ventajas económicas, legales, contables o tributarias, o sobre las consecuencias de realizar o no determinada transacción. Este correo electrónico y/o el material adjunto deben contener información de carácter institucional, vinculada a las labores del remitente, en caso no tenga(n) dicha condición, el mismo es de exclusiva responsabilidad del remitente, sin responsabilidad alguna para Sociedad Suizo Peruana de Embutidos S.A. - SUPEMSA”.
Afternoon All,
If you run a query such as your first one, in Toad, it is the database that does the execution, Toad simply passes what you ask to execute to the database. So, when you execute
select * from my_table where campo_fecha >= to_date(‘01/10/2017’);
Oracle will convert the passed string literal ‘01/10/2017’ to a date using your user’s default NLS_DATE_FORMAT string - and it is possible that the default for the database has been changed since you upgraded Toad. You, as a developer (I assume) have no control over what your DBAs do with default database parameters. All my databases, for example, were always set to ‘dd/mm/yyyy hh24:mi:ss’ as the default for the database. If people wanted to change that, they had to execute an alter session set nls_date_format=whatever; - and that assumed that they had been granted alter session privileges.
So, you need to find out what your database’s default NLS_DATE_FORMAT has been set to. On the other hand, Oracle will try to convert string literals, as above, using the default format, but will then attempt to convert it using some other (unknown) formats until it finds one that works, or, they all fail. So, the above date string could be converted as 1st October 2017 00:00:00 or 10th January 2017 00:00:00 if the USA’s default date format of mm/dd/yyyy takes precedence over whatever other formats are used by Oracle to convert a date string like yours, where no required format has been supplied.
So, the question has to be, what is the database converting your string to as a date? October or January?
select * from nls_session_parameters where parameter = ‘NLS_DATE_FORMAT’;
Will show you the default date format for your session, if you have access to NLS_SESSION_PARAMETERS that is.
So, back to your date string, January or October? How to tell?
In a database with the date format set to ‘DD-MON-RR’ I get an error telling me that 10 is not a valid month. Makes sense, however, Oracle seems to have failed to convert the string to a date.
In a database where I have set my default to be ‘dd/mm/yyyy hh24:mi:ss’, I get no errors, and select to_date(‘01/10/2017’) from dual; gives me exactly that, ‘01/10/2017 00:00:00’ so it appears to be October, for me at least. Better check though:
If I now select to_char(to_date(‘01/10/2017’), ‘dd-Month-yyyy’) from dual; then I get, as expected, ‘01-October -2017’ so it’s converting correctly and not attempting to go down the route of the USA’s much favoured but extremely weird date format! 
So, do you see the problem here? You don’t know what the DBAs have set up as the default date format, and you are assuming/expecting it to be something that all your dates will convert with - which might not always be the case. My advice to you, and always has been to any developers I’ve worked with, is to always be explicit as you never know when things might change underneath you, without you knowing. You should therefore always tell TO_DATE() exactly what format your date strings are in, so that it (a) doesn’t have to guess and (b) shouldn’t raise any errors for certain dates that you pass.
Is it possible therefore that your DBAs have changed something? Did they upgrade the database perhaps to a new version? Did they apply a patch perhaps? Has some other application started using the database, and has required a default date format change?
There are numerous ways that we DBAs can seriously mess up developers’ lives, without even trying! If you are always explicit in your date strings and tell TO_DATE() exactly what format the strings are in, then nothing we DBAs can do will upset your code. If you don’t want to always be explicit, then try running something like alter session set nls_date_format=‘dd/mm/rrrr’ (which seems to be what you want - but beware of any time portions in your date literals if you do) and then you don’t have to tell TO_DATE() the required format, it will use your session default of ‘DD/MM/RRRR’ or whatever you set.
Finally, there are rows in my_table where campo_fecha is indeed greater than or equal to 1st October 2017? Because you didn’t mention that your execution of the statement was giving an error, so Oracle must have converted the string to a date, of some kind, and compared it with the column. Try running the statement(s) in SQL*Plus and check if you get a message like ‘no rows returned’ which would indicate a table with no rows matching your date. Equally, in Toad, go to Database->Spool SQL -> to screen then execute the failing statement again. At the bottom of the screen you will see exactly what Toad sent to Oracle to be executed, and what happened to it when it got there.
Have fun.
HTH.
Hello lhernandez_75862,
The format of your second query is always – always, Always, ALWAYS – a better one than the first. And you’ve just found out why – because your Oracle Client (not the server!) formats dates.
What happens if the default format changes from ‘DD/MM/RRRR’ to ‘MM/DD/RRRR’ on your client or that’s the default for someone else’s client that runs that query? Disaster! Suddenly October 1st becomes January 10th (‘01/10/2017’ to ‘10/01/2017’)! Eep!
Now that you know the dangers, if you still want to take shortcuts, there’s a few ways to do this. When you upgraded Toad to version 12.12, is it possible that it’s using a different ORACLE_HOME on your PC? You can store your preferred date format in the Registry on your PC. See https://docs.oracle.com/database/121/NTQRF/registry.htm#NTQRF415 Remember that your shortcut may not work correctly on another client, like when you get a new PC or a new version/installation of the Oracle Client.
You can also set the NLS_DATE_FORMAT directly in your session. A quick Google search will show you how to do that as well.
Test everything out of course. But, as I said, it is always best to explicitly specify the date format.
Good luck!
Rich
I should have know you were replying, Norm! Puts my measly post to shame! 
Luis,
I’ve just noticed that your second query returns the desired rows. This does indicate that perhaps the database’s default date format is set differently to how it used to be.
HTH
Hi Rich,
I’m not working at the moment, so I have time to stick my nose into other people’s business, at great length!
Thanks for answer.
Is correct, the default setting for date format in Database is 'DD-MON-YY'.
But it's setting was like this and previous version Toad my sentence working.
I understand is better put format.
Very thanks for all.
--
Luis E. Hernández Manzanares
Soporte Sistemas de Gestión
**Sociedad Suizo Peruana ****de ****Embutidos S.A. **
SUPEMSA
Comunidad Industrial 240 - Chorrillos
Central: (51) 1 336-7000 Anexo: 1248
Celular: (51) 998 106 098
Atención de Soporte Técnico y Sistemas y Procedimientos de Sistemas
2017-10-18 7:37 GMT-05:00 Norm [TeamT] bounce-NormTeamT@toadworld.com:
Reply by Norm [TeamT]
Afternoon All,
If you run a query such as your first one, in Toad, it is the database that does the execution, Toad simply passes what you ask to execute to the database. So, when you execute
select * from my_table where campo_fecha >= to_date('01/10/2017');
Oracle will convert the passed string literal '01/10/2017' to a date using your user's default NLS_DATE_FORMAT string - and it is possible that the default for the database has been changed since you upgraded Toad. You, as a developer (I assume) have no control over what your DBAs do with default database parameters. All my databases, for example, were always set to 'dd/mm/yyyy hh24:mi:ss' as the default for the database. If people wanted to change that, they had to execute an alter session set nls_date_format=whatever; - and that assumed that they had been granted alter session privileges.
So, you need to find out what your database's default NLS_DATE_FORMAT has been set to. On the other hand, Oracle will try to convert string literals, as above, using the default format, but will then attempt to convert it using some other (unknown) formats until it finds one that works, or, they all fail. So, the above date string could be converted as 1st October 2017 00:00:00 or 10th January 2017 00:00:00 if the USA's default date format of mm/dd/yyyy takes precedence over whatever other formats are used by Oracle to convert a date string like yours, where no required format has been supplied.
So, the question has to be, what is the database converting your string to as a date? October or January?
select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';
Will show you the default date format for your session, if you have access to NLS_SESSION_PARAMETERS that is.
So, back to your date string, January or October? How to tell?
In a database with the date format set to 'DD-MON-RR' I get an error telling me that 10 is not a valid month. Makes sense, however, Oracle seems to have failed to convert the string to a date.
In a database where I have set my default to be 'dd/mm/yyyy hh24:mi:ss', I get no errors, and select to_date('01/10/2017') from dual; gives me exactly that, '01/10/2017 00:00:00' so it appears to be October, for me at least. Better check though:
If I now select to_char(to_date('01/10/2017'), 'dd-Month-yyyy') from dual; then I get, as expected, '01-October -2017' so it's converting correctly and not attempting to go down the route of the USA's much favoured but extremely weird date format!
So, do you see the problem here? You don't know what the DBAs have set up as the default date format, and you are assuming/expecting it to be something that all your dates will convert with - which might not always be the case. My advice to you, and always has been to any developers I've worked with, is to always be explicit as you never know when things might change underneath you, without you knowing. You should therefore always tell TO_DATE() exactly what format your date strings are in, so that it (a) doesn't have to guess and (b) shouldn't raise any errors for certain dates that you pass.
Is it possible therefore that your DBAs have changed something? Did they upgrade the database perhaps to a new version? Did they apply a patch perhaps? Has some other application started using the database, and has required a default date format change?
There are numerous ways that we DBAs can seriously mess up developers' lives, without even trying! If you are always explicit in your date strings and tell TO_DATE() exactly what format the strings are in, then nothing we DBAs can do will upset your code. If you don't want to always be explicit, then try running something like alter session set nls_date_format='dd/mm/rrrr' (which seems to be what you want - but beware of any time portions in your date literals if you do) and then you don't have to tell TO_DATE() the required format, it will use your session default of 'DD/MM/RRRR' or whatever you set.
Finally, there are rows in my_table where campo_fecha is indeed greater than or equal to 1st October 2017? Because you didn't mention that your execution of the statement was giving an error, so Oracle must have converted the string to a date, of some kind, and compared it with the column. Try running the statement(s) in SQL*Plus and check if you get a message like 'no rows returned' which would indicate a table with no rows matching your date. Equally, in Toad, go to Database->Spool SQL -> to screen then execute the failing statement again. At the bottom of the screen you will see exactly what Toad sent to Oracle to be executed, and what happened to it when it got there.
Have fun.
HTH.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
Aviso de Confidencialidad y Protección de Datos Personales:
Este correo electrónico y/o el material adjunto es para uso exclusivo de la persona o entidad a la que expresamente se le ha enviado, y puede contener información confidencial y/o material privilegiado y/o protegido por la Ley de Protección de Datos Personales. Si usted es el destinatario legítimo del mismo, adicionalmente le indicamos que toda la información que nos brinde y que se considere como “datos personales” y/o “datos sensibles” según lo dispuesto en la Ley de Protección de Datos Personales, serán tratados de manera reservada y confidencial conforme lo establece la referida Ley y nuestro marco constitucional (Dar “click” para leer condiciones y “ACEPTAR” o “NO ACEPTAR” las mismas previamente leídas). Si usted no es el destinatario legítimo del mismo, por favor repórtelo inmediatamente al remitente del correo y bórrelo. Cualquier revisión, retransmisión, difusión o cualquier otro uso de este correo, por personas o entidades distintas a las del destinatario legítimo, queda expresamente prohibido. Este correo electrónico no pretende ni debe ser considerado como constitutivo de ninguna relación legal, contractual o de otra índole similar, en consecuencia, no genera obligación alguna a cargo de su emisor o su representada. En tal sentido, nada de lo señalado en esta comunicación o en sus anexos podrá ser interpretado como una recomendación sobre los riesgos o ventajas económicas, legales, contables o tributarias, o sobre las consecuencias de realizar o no determinada transacción. Este correo electrónico y/o el material adjunto deben contener información de carácter institucional, vinculada a las labores del remitente, en caso no tenga(n) dicha condición, el mismo es de exclusiva responsabilidad del remitente, sin responsabilidad alguna para Sociedad Suizo Peruana de Embutidos S.A. - SUPEMSA”.
Hi Luis,
Ok, I’m now puzzled. If the default date format was (always) DD-MON-YY then you should always have had errors when you tried to execute a TO_DATE(‘01/10/2017’) - as indeed I did when I set mine to that format - as this SQL*Plus example shows:
SQL> alter session set nls_date_format=‘DD-MON-YY’;
Session altered.
SQL> select to_date(‘01/10/2017’) from dual;
select to_date(‘01/10/2017’) from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Just out of interest, what is the version of the database please?
I have an old 11.2.0.2.0 database and the deafualt there is DD-MON-RR. I know that in the old days of Oracle 9i, it was DD-MON-YY but I thing they changed it to RR at some point.
Regardless, that also gives the same error as the above example, 10 is not a valid month.
Cheers,
Norm.[TeamT]
10.2
Cheers,
--
Luis E. Hernández Manzanares
Soporte Sistemas de Gestión
**Sociedad Suizo Peruana ****de ****Embutidos S.A. **
SUPEMSA
Comunidad Industrial 240 - Chorrillos
Central: (51) 1 336-7000 Anexo: 1248
Celular: (51) 998 106 098
Atención de Soporte Técnico y Sistemas y Procedimientos de Sistemas
2017-10-18 9:44 GMT-05:00 Norm [TeamT] bounce-NormTeamT@toadworld.com:
Reply by Norm [TeamT]
Just out of interest, what is the version of the database please?
I have an old 11.2.0.2.0 database and the deafualt there is DD-MON-RR. I know that in the old days of Oracle 9i, it was DD-MON-YY but I thing they changed it to RR at some point.
Regardless, that also gives the same error as the above example, 10 is not a valid month.
Cheers,
Norm.[TeamT]
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag this post as spam/abuse.
Aviso de Confidencialidad y Protección de Datos Personales:
Este correo electrónico y/o el material adjunto es para uso exclusivo de la persona o entidad a la que expresamente se le ha enviado, y puede contener información confidencial y/o material privilegiado y/o protegido por la Ley de Protección de Datos Personales. Si usted es el destinatario legítimo del mismo, adicionalmente le indicamos que toda la información que nos brinde y que se considere como “datos personales” y/o “datos sensibles” según lo dispuesto en la Ley de Protección de Datos Personales, serán tratados de manera reservada y confidencial conforme lo establece la referida Ley y nuestro marco constitucional (Dar “click” para leer condiciones y “ACEPTAR” o “NO ACEPTAR” las mismas previamente leídas). Si usted no es el destinatario legítimo del mismo, por favor repórtelo inmediatamente al remitente del correo y bórrelo. Cualquier revisión, retransmisión, difusión o cualquier otro uso de este correo, por personas o entidades distintas a las del destinatario legítimo, queda expresamente prohibido. Este correo electrónico no pretende ni debe ser considerado como constitutivo de ninguna relación legal, contractual o de otra índole similar, en consecuencia, no genera obligación alguna a cargo de su emisor o su representada. En tal sentido, nada de lo señalado en esta comunicación o en sus anexos podrá ser interpretado como una recomendación sobre los riesgos o ventajas económicas, legales, contables o tributarias, o sobre las consecuencias de realizar o no determinada transacción. Este correo electrónico y/o el material adjunto deben contener información de carácter institucional, vinculada a las labores del remitente, en caso no tenga(n) dicha condición, el mismo es de exclusiva responsabilidad del remitente, sin responsabilidad alguna para Sociedad Suizo Peruana de Embutidos S.A. - SUPEMSA”.
Hi Luis,
I have had a quick look in the 10.2 docs and found, because I’m interested (and possibly a saddo!) that in 10.2, and upwards, certain NLS defaults are set based on the setting of NLS_TERRITORY and NLS_LANGUAGE. These, in turn default to whatever NLS_LANG is set to in the shell on the database server. If your server is Unix/Linux then setting NLS_LANG=language_territory.characterset will define the date format default, the names of the days of the week/months of the year/error messages etc.
I usually have something like NLS_LANG=“ENGLISH_UNITED KINGDOM.WE8ISO8859P15” which gives me a default date format of ‘DD-MON-RR’ but if I set NLS_LANG to “GERMAN_BELGIUM.WE8ISO8859P15” I get a default date format of ‘DD/MM/RR’ instead.
Obviously, these are set up in the absence of the DBA actually setting a format in the spfile/parameter file used for starting the database.
Prior to 10.2 only NLS_TERRITORY was considered, not NLS_LANGUAGE but apparently, some countries (territories) have multiple languages! Who would have though?
Cheers.
Norm. [TeamT]
