473,144 Members | 2,487 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,144 software developers and data experts.

ORA-01840: input value not long enough for date format

I'm trying to perform a select query on an oracle database but I get
this error.

ORA-01840: input value not long enough for date format

My query is:

SELECT * FROM TIGER.VIEW_TNW_MAINSTREAM WHERE
TO_CHAR(SYSDATE,'yyyy/mm/dd') =
TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd')

Does anyone know what's wrong?

When I do:

SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM TIGER.VIEW_TNW_MAINSTREAM

and

SELECT TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd') FROM
TIGER.VIEW_TNW_MAINSTREAM

i get no errors.

Would it be the fact that I have NULL values in the DUE_DATE field?

Thanks in advance for any help.

Tim :-)
Jul 19 '05 #1
1 36124
Tim,

If you write select statements as you did here, you will get into trouble
with your code in the near future.

Anyway, try:

SELECT *
FROM tiger.view_tnw_mainstream
WHERE trunc(due_date-5) = trunc(sysdate)

It is expected that column DUE_DATE is a date column.

HTH,

--
Jasper Scholten
DBA / Application Manager / Systems Engineer
"ck388" <go****@timyee.com> schreef in bericht
news:69**************************@posting.google.c om...
I'm trying to perform a select query on an oracle database but I get
this error.

ORA-01840: input value not long enough for date format

My query is:

SELECT * FROM TIGER.VIEW_TNW_MAINSTREAM WHERE
TO_CHAR(SYSDATE,'yyyy/mm/dd') =
TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd')

Does anyone know what's wrong?

When I do:

SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd') FROM TIGER.VIEW_TNW_MAINSTREAM

and

SELECT TO_CHAR(TO_DATE(DUE_DATE,'yyyy/mm/dd')-5,'yyyy/mm/dd') FROM
TIGER.VIEW_TNW_MAINSTREAM

i get no errors.

Would it be the fact that I have NULL values in the DUE_DATE field?

Thanks in advance for any help.

Tim :-)

Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Galina | last post by:
Hello I have created a trigger not to let records with certain fields null to be inserted into the table: BEGIN IF :new.RUL_CODE is null or :new.FES_UINS_CODE is null or :new.FES_UIO_CALOCC is...
6
by: John | last post by:
Hi Right password -> ORA-12154: TNS:Could not resolve service name wrong password => ORA-01017: invalid username/password; logon denied Tested on a Windows XP client connecting to Oracle on...
1
by: Park Yeon Jo | last post by:
About Error : ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor I installed Oracle 8.1.7 on Windows XP Professional. and I wanto connect to that server...
1
by: Adam Ruth | last post by:
I'm using OCI on Mac OS X and I've run into a strange problem with my TNSNAMES.ORA file. My TNSNAMES.ORA file has one entry INV4II and it works fine. However, it will only work if that is the...
6
by: bdj | last post by:
Hello! I have at set of tnsnames.ora. I wich to make an union, e.g. a single file of it. How can I do that easy? Greetings Bjørn
2
by: mpatel6 | last post by:
I had this error in alert log and my instance was down, anybody can help me? Errors in file /u01/app/oracle/admin/sotstest/bdump/sotstest_p004_626740.trc: ORA-07445: exception encountered: core...
2
by: Dwie | last post by:
Dear All, I'm a new in Oracle DB. I'm using Windows 2000 as server and Windows XP as client. I installed Oracle Enterprise on server and Oracle 8.1 on clients. I have some client with 2 different...
0
by: basmgokul | last post by:
I am using oracle 10g in windows vista.. when starting DB it throws an error Errors in file c:\database\udump\practice_ora_440.trc: ORA-00704: bootstrap process failure ORA-39700: database...
1
by: michael ngong | last post by:
michael.john@gmx.at (Michael John) wrote in message news:<90cc4edd.0306230900.28075193@posting.google.com>... MIchael I you stated the OS and platform that could make it easier to address your...
0
by: wolfee | last post by:
I'm running 10gR2 RAC in Prod, Test and DEV and after doing a clone to the DEV environment I'm getting a wierd ORA-12514 error for only one node. I can tnsping the node and it comes back fine. ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
by: efe2023 | last post by:
Hello Everybody, There is SQL Server 2008 R2 database with MS Access front-end (linked ODBC tables). I can see all linked tables in MS Access and can add new rows. However, when trying to update...
3
by: jimatqsi | last post by:
So, I had this bright idea today that didn't quite work out. Or maybe it is working out but requires me to change a habit. I'm working on a form that will be a sub-form of another form. During...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.