Toad World® Forums

SQL query runs fine in TOAD, but not on UNIX (sqlplus) [defined as DATE]


#1

SQL query runs fine in TOAD, but not on UNIX (sqlplus) [defined as DATE]


#2

On Wed, May 26, 2010 at 4:02 PM, wrote:

Hi

Hi Rob

thanks

Try this
[…]
WHERE ess_user = ‘suite_admin_sharepoint’ AND upd_time > to_date(‘25/05/2010’,‘DD/MM/YYYY’)
ORDER BY serial DESC;

Why Toad accept the query without “TO_DATE” and sqlplus not?

cheers Sven


#3

Without TO_DATE, Oracle converts the character string to a date by the
currently defined session default date format. Your Toad session
probably has a different default date format than your SQLPlus session,
and the character string you were supplying matched the format used by
Toad and didn’t match the format used by SQL
Plus.

Nate Schroeder
US Seed & Trait Commercial IT - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592


#4

On Wed, May 26, 2010 at 4:48 PM, SCHROEDER, NATHAN E [AG/1000]
wrote:

Without TO_DATE, Oracle converts the character string to a date by the
currently defined session default date format. Your Toad session
probably has a different default date format than your SQLPlus session,
and the character string you were supplying matched the format used by
Toad and didn’t match the format used by SQL
Plus.

Hi Nathan

thanks for explanation. Yes this was the reason US time format vs.
European format.

cheers
Sven