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

Hi folks

In Toad 8.6 this SQL returns 34 rows without error.

UPD_TIME is DATE.

SELECT serial
|| ‘;’
|| upd_time
|| ‘;’
|| MESSAGE
|| ‘;’
|| entity_table
|| ‘;’
|| action
|| ‘;’
|| status
|| ‘;’
|| act_msg
|| ‘;’
|| rss_name
|| ‘;’
|| rss_type
|| ‘;’
|| ADMIN
FROM trans
WHERE ess_user = ‘suite_admin_sharepoint’ AND upd_time > ‘25.05.2010’
ORDER BY serial DESC;

When I copy and paste the SQL script to UNIX. I receive error:

Error:

serial; upd_time; message; entity_table; action; status; act_msg;
rss_name; rss_type; admin
WHERE ess_user = ‘suite_admin_sharepoint’ AND upd_time > ‘25.05.2010’

ERROR at line 21:
ORA-01843: not a valid month

why and how to fix?

cheers
Sven

You need to use the TO_DATE function. Look it up….

Hi Sven,

Ah, DATEs and the default date format, one of my favourites!

WHERE .... AND upd_time > '25.05.2010'
ORDER BY serial DESC;

When I copy and paste the SQL script to UNIX. I receive error:

The default date format (NLS_DATE_FORMAT) is not the same in your client
running Toad and in the client on the server. You can check this by
connecting to the same database using Toad, and SQL*Plus on the server
and running this:

select sysdate from dual;

You will most likely see a difference.

The basic rule with DATEs is simple, never assume a default date format
will be what you think it will be!

So, always use TO_DATE() or TO_CHAR() to convert the date into a known
format before making any checks. However, don't forget that if you apply
any functions to the operands on the left side of the comparison
operator, you will prevent the use of any indices that may be defined
(unless said indices are function based and exactly the same as the
function you are applying).

So this will lose index abilities:

WHERE TO_CHAR(upd_time, 'dd.mm.yyyy') > '25.05.2010'

But this will allow indices to be used:

WHERE upd_time > TO_DATE('25.05.2010', 'dd.mm.yyyy')

In order to make sure that indices can be used, never convert the column
to match the literal, always convert the literal to match the column.

Good luck.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk