SQL Developer


Date/Time Settings Causing "Invalid Number" with TRUNC?


Good Morning, I have a particular query (below) that executes successfully in certain sessions within SQL Developer, but not others. In the other sessions, an 'Invalid Number' error returns, unless one of the 'TRUNC' conditions is removed (only the TRUNC). Unfortunately, the only thing I can guess is the particular date/time settings associated with the user's SQL Developers session do not support the formats associated below, which I realize is a long shot. The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'. Could the TO_CHAR '24HH' statement be causing the issue? I wanted to get some thoughts from some experts and determine if an NLS_DATE_FORMAT is really required here (especially since it executes elsewhere) or if there is a particular setting (date/time format) that can be utilized? SELECT  TO_CHAR(DATE_1, 'YYYY-MM-DD') FIELD_1,  TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2FROM  TABLE_1AND FIELD_1 >= TRUNC(SYSDATE)-1AND FIELD_1 < TRUNC(SYSDATE) Thanks in advance for any help!
1070f1fe-d537-47ef-aec3-00d8cc4d7d57 wrote:
 
I have a particular query (below) that executes successfully in certain sessions within SQL Developer, but not others. In the other sessions, an 'Invalid Number' error returns, unless one of the 'TRUNC' conditions is removed (only the TRUNC). Unfortunately, the only thing I can guess is the particular date/time settings associated with the user's SQL Developers session do not support the formats associated below, which I realize is a long shot. The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'. Could the TO_CHAR '24HH' statement be causing the issue? I wanted to get some thoughts from some experts and determine if an NLS_DATE_FORMAT is really required here (especially since it executes elsewhere) or if there is a particular setting (date/time format) that can be utilized?
 
SELECT
  TO_CHAR(DATE_1, 'YYYY-MM-DD') FIELD_1,
  TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2
FROM
  TABLE_1
AND FIELD_1 >= TRUNC(SYSDATE)-1
AND FIELD_1 < TRUNC(SYSDATE)
Thanks for posting the detailed info.
The current setting for the date format is 'DD-MON-YY HH:mm:ss AM'.
That isn't going to work. You are using 'mm' (which is MONTH) in the time field for minutes.
  TO_CHAR(DATE_1, 'HH24:MI:SS') FIELD_2
See how that one uses the correct 'MI'?
Thanks for the correction rp. I was going by memory (was looking into another user's session), so I was wrong here.Using the assumption that the format is correct in the other user's session (with 'MI'), I am unable to comprehend the outcome here as it seems very strange. Delete 1 of the 2 'TRUNC' statements and the script works? In addition, using the same script in other environments works successfully. Have yet to run into this issue, granted I'm by no means an expert, but this isn't making any since 
Thanks again rp for your insight. It ended up being due to a field discrepancy between the environments. There are some restrictions on my side to the visibility, hence I added an irrelevant post here. Hope I didn't cause too much of your time! Cheers.

Related Links

Increasing  Of Reported Issues' Number
SQL Developer for Oracle install on Vista32
Dropping Java Classes
Publish to Apex
datamodeler: case
Unable to use backspace key in SQL Developer
Import from CSV issue
issues with connection busy functionality
SQLDeveloper empty dialogbox
Connection Timed Out while using sqldeveloper Debugger
DDL generation problems
Risk of using SQLDeveloper in a pharmaceutical environment
Driver library error
Enable/Disable substitution variables...
COLLECT bug?
Popup Describe on objects for other schemas

Categories

ORACLE DEV
WebCenter Content
Text
Globalization Support
Designer Headstart
ODBC
Industries Archived Fo...
Calendar SDK
Export/Import/SQL Load...
XQuery
Enterprise Manager
Email Server
Desktop Datacenter
TimesTen In-Memory Dat...
JSP (Korean)
OC4J (Korean)
EJB (Korean)
NLS (Korean)
ODBC (Korean)
Discoverer (Korean)
Technical Bulletin (Ko...
Technologies - JSP (Ko...
PeopleSoft Enterprise
Contact Center and Ser...
Configurator
Master Data Management...
Oracle Virtualization
Logistics
QA/Testing
WebLogic
weblogic.developer.int...
weblogic.developer.int...
aqualogic.bpm.development
Remote APIs
Portlets
liquid.developer.inter...
SQL Developer Data Mod...
企业管理器
Industries
BEA General (Chinese)
Primavera Systems Tech...
Primavera EVM User Com...
PeopleSoft OVM Templates
AIA 3.0 Early Adopters
Designer (Korean)
Oracle Solaris
Networking
JavaFX 1.x and JavaFX ...
Serialization [ARCHIVE]
Collections: Lists, Se...
Reflections & Referenc...
Java Sound
Java Virtual Machine (...
JavaHelp System [ARCHIVE]
Javadoc Tool (Develope...
Java 3D
Algorithms
JavaMail
WebCenter Archived Forums
Java Business Integrat...
Solaris Networking
Java TV
Java Programming [Arch...
Blades General Discussion
Distributed Real-Time
Java System Portal Ser...
Business
Exalogic Elastic Cloud
Calc Manager
JD Edwards EnterpriseO...
Customer Advisory Board
Oracle Mobile
Oracle Labs
Build
Infrastructure as a Se...
General Database Discu...
Oracle Fusion Middlewa...
Marketing Job Board
Austin Eloqua Users
Eloqua Users - San Diego
United Kingdom User Group
Eloqua Users with MS C...
Cloud Component Beta C...
SRM/Eloqua users
OTN América Latina Tou...
Darios Sonera group
OMC - Industry Solutio...
Java SE Early Access
Topliners Tips and Tricks
Global Trade Management
Supply Planning
Oracle EMEA Partner Sales
Oracle Learning Library
Solver
Home of Oracle Certifi...
Adopt OpenJDK
Peru
Romanian Oracle User G...
From MOOC1
DBA Gruppe
SalesCloudExtensions
Eloqua Basic Learner
Oracle Datasource for ...
Generic Linux
Financials
Human Resource Managem...
Life Sciences
Products (Korean)
GRC General Discussion
Install/Upgrade/Manage
Enterprise 2.0
WebLogic Server - General
System Management and ...
jrockit.developer.inte...
Telecom Technology (Ch...
Java Card
Enterprise Manager Ops...
Oracle Enterprise Mana...
Duke's Choice Awards

Resources

Encrypt Message