Solved

Use of TO_DATE function in Where Clause.

Posted on 2008-06-10
43
2,207 Views
Last Modified: 2013-12-19
I have a view called VW_PH_INTERFACE created which includes the following column:
DATE_TIME_OF_ANALYSIS VARCHAR2(1024)

This column contains dates in the format - DD.MM.YYYY HH24:MI   (Example - 20.06.2007 17:29).

I can run a query of the form:

SELECT  to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi') from FROM vw_ph_interface

This will return a list of dates in the following format:

20/06/2007 17:26:00

I am trying to filter these dates using the following query but cannot get the query to execute:

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi') > '01/01/2007 16:15:00'

I get the following error: ORA-01861: literal does not match format string

I also tried including the SS in the query as follows:

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM ngsdms60.vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi:ss') > '01/01/2007 16:15:00'

Still the same error... what am I doing wrong here as I cannot figure out how this will not work!!!
0
Comment
Question by:pokeeffe
  • 18
  • 15
  • 5
  • +3
43 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
your constant needs a TO_DATE() also:
SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi') >  to_date ('01/01/2007 16:15:00',  'dd.mm.yyyy hh24:mi') 

Open in new window

0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
    Hi

Change the query to this:

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM ngsdms60.vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi:ss') > to_date('01.01.2007 16:15:00', 'dd.mm.yyyy hh24:mi:ss')

Regards,
   Tomas Helgi
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
or you can even say :

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi') >
to_date('01.01.2007 16:15','dd.mm.yyyy hh24:mi');

you do not need a seconds 'SS' in the where clause because your column does not have data for seconds and it just has 'DD.MM.YYYY HH24:MI'
0
 

Author Comment

by:pokeeffe
Comment Utility
Same error with all of the above queries...

ORA-01861: literal does not match format string
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
i think your date_time_of_analysis varchar2 column is having some bad data which does not match this format 'dd.mm.yyyy hh24:mi'

Can you just do a select and see whether all the data this column contains is valid and is in the 'dd.mm.yyyy hh24:mi' format otherwise you will get that error and that is the reason why most of the oracle applications use a DATE/SYSTIMESTAMP datatypes in oracle rather than varchar2 to store date/time information.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
try

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_CHAR (date_time_of_analysis, 'dd/mm/yyyy hh24:mi') > '01/01/2007 16:15:00'
--TO_CHAR used and . replaced with /

meikl ;-)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
my guess is that some records for this column might be having / instead of . as a separator or some records might even have date information in a different format....

probably run some queries to validate the data in your column like
--> how many . are there in that column which should always be 2  ( dd.mm.yyyy )
--> how many : are there which should be 1
--> length of the column which can give some other clues as well...
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
select date_time_of_analysis
from vw_ph_interface;

can you paste the output of the above query for the first 20 to 30 records , just to see how your data looks like...
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
sorry overlloked another mismatch too

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_CHAR (date_time_of_analysis, 'dd/mm/yyyy hh24:mi') > '01/01/2007 16:15:00'
--TO_CHAR used and . replaced with /

the where-clause should be
WHERE to_CHAR (date_time_of_analysis, 'dd/mm/yyyy hh24:mi') > '01/01/2007 16:15'

or

WHERE to_CHAR (date_time_of_analysis, 'dd/mm/yyyy hh24:mi:ss') > '01/01/2007 16:15:00'

meikl ;-)


0
 

Author Comment

by:pokeeffe
Comment Utility
Data Looks fine... I have included a subset of the data but I have checked the full data set.

select date_time_of_analysis
from vw_ph_interface;

20.06.2007  17:29
20.06.2007  17:11
16.04.2007  10:55
20.06.2007  17:18
28.06.2007  15:43
02.07.2007  15:18
02.07.2007  15:18
02.07.2007  15:18
04.07.2007  10:49
20.06.2007  17:18
05.09.2007  11:23
05.09.2007  11:29
28.08.2007  12:22
28.08.2007  14:22
28.08.2007  12:43

SELECT to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM ngsdms60.vw_ph_interface

20/06/2007 17:29:00
20/06/2007 17:11:00
16/04/2007 10:55:00
20/06/2007 17:18:00
28/06/2007 15:43:00
02/07/2007 15:18:00
02/07/2007 15:18:00
02/07/2007 15:18:00
04/07/2007 10:49:00
20/06/2007 17:18:00
05/09/2007 11:23:00
05/09/2007 11:29:00
0
 

Author Comment

by:pokeeffe
Comment Utility
Lenght (date_time_of_analysis) returns 17, above is 16 ??
0
 

Author Comment

by:pokeeffe
Comment Utility
SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM ngsdms60.vw_ph_interface
WHERE to_CHAR (date_time_of_analysis, 'dd/mm/yyyy hh24:mi') > '01/01/2007 16:15'

gives another error: ORA-01481: invalid number format model

But I thought I should convert to a date to be able to apply where clause to filter the records.
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
what kind of type is your date_time_of_analysis-Field?
0
 

Author Comment

by:pokeeffe
Comment Utility
VARCHAR2(1024)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
can you try :

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy  hh24:mi') >
to_date('01.01.2007 16:15','dd.mm.yyyy  hh24:mi');
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
in this case you have to use to_date twice or just left it out like

WHERE to_date (date_time_of_analysis, 'dd/mm/yyyy hh24:mi') > to_date('01/01/2007 16:15', 'dd/mm/yyyy hh24:mi')

a left out makes only sense on an equal compare

WHERE date_time_of_analysis = '01/01/2007 16:15'

meikl ;-)
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
there are two space in between

28.08.2007  12:43

and hence i have provided a modified query in my previous update. Try that one and let me know if it works.
0
 

Author Comment

by:pokeeffe
Comment Utility
SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy  hh24:mi') >
to_date('01.01.2007 16:15','dd.mm.yyyy  hh24:mi');

Gives error:

ORA-01861: literal does not match format string
0
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
as nav_kum_v already commented

ensure if one or two spaces are needed between date and time (hard to see here)

here for one space

WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi') >
to_date('01.01.2007 16:15','dd.mm.yyyy hh24:mi');
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
try this :

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy  hh24:mi') >
to_date('01.01.2007  16:15','dd.mm.yyyy  hh24:mi'); -- i have added on more space to the literal
0
 

Author Comment

by:pokeeffe
Comment Utility
I can see now that the original data contains 2 spaces between the date and time... however still getting the same error... and the conversion on the data above in the select query still works fine.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
did you try my last one query:

SELECT batchid, to_date (date_time_of_analysis, 'dd.mm.yyyy hh24:mi')
FROM vw_ph_interface
WHERE to_date (date_time_of_analysis, 'dd.mm.yyyy  hh24:mi') >
to_date('01.01.2007  16:15','dd.mm.yyyy  hh24:mi'); -- i have added one more space to the literal

This should work without any errors..
0
 

Author Comment

by:pokeeffe
Comment Utility
Yes nav_kum_v I did try this and still the same error... I cut and paste your query exactly... very bizarre!
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
then it means that all your data is not in the same format with 2 spaces..

run this query then

select length(date_time_of_analysis), count(1)
from  vw_ph_interface
group by  length(date_time_of_analysis) ;

Paste the output of this...
0
 

Author Comment

by:pokeeffe
Comment Utility
The output is as follows:

Length, Count(1)
17, 137
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
is it possible to paste the output of the below query for all the 137 records..

select date_time_of_analysis
from vw_ph_interface;
0
 

Author Comment

by:pokeeffe
Comment Utility
All records...

20.06.2007  17:29
20.06.2007  17:11
16.04.2007  10:55
20.06.2007  17:18
28.06.2007  15:43
02.07.2007  15:18
02.07.2007  15:18
02.07.2007  15:18
04.07.2007  10:49
20.06.2007  17:18
05.09.2007  11:23
05.09.2007  11:29
28.08.2007  12:22
28.08.2007  14:22
28.08.2007  12:43
04.07.2007  10:27
04.07.2007  10:27
04.07.2007  10:27
04.07.2007  10:49
04.07.2007  10:49
05.09.2007  11:23
06.11.2007  12:30
07.11.2007  11:28
08.11.2007  14:31
06.11.2007  12:30
07.11.2007  11:28
08.11.2007  14:31
09.11.2007  13:27
12.11.2007  10:37
14.11.2007  14:43
06.11.2007  14:19
07.11.2007  11:36
09.11.2007  13:35
12.11.2007  10:44
14.11.2007  14:48
13.11.2007  15:34
16.11.2007  14:16
16.11.2007  10:51
16.11.2007  11:27
16.11.2007  11:33
03.12.2007  09:13
03.12.2007  20:23
04.12.2007  09:05
04.12.2007  20:25
05.12.2007  09:42
05.12.2007  10:05
05.12.2007  20:21
06.12.2007  08:28
07.12.2007  08:00
07.12.2007  08:00
07.12.2007  09:03
09.12.2007  20:32
10.12.2007  20:05
11.12.2007  19:57
01.12.2007  11:30
04.12.2007  09:05
06.12.2007  08:28
05.09.2007  13:04
02.12.2007  20:11
05.12.2007  09:52
07.12.2007  08:07
06.12.2007  20:30
12.09.2007  17:07
05.09.2007  13:04
02.07.2007  15:18
02.12.2007  11:57
03.12.2007  11:04
04.12.2007  08:52
04.12.2007  17:18
04.12.2007  17:18
05.12.2007  09:52
05.12.2007  20:21
06.12.2007  08:34
07.12.2007  08:07
08.12.2007  09:06
10.12.2007  09:19
11.12.2007  10:01
30.11.2007  18:03
02.12.2007  08:52
05.12.2007  09:42
07.12.2007  08:00
01.12.2007  20:28
04.12.2007  17:18
06.12.2007  08:34
05.12.2007  20:21
02.07.2007  15:18
02.07.2007  15:18
14.01.2008  12:02
16.01.2008  13:47
08.01.2008  15:34
23.01.2008  16:21
18.01.2008  17:03
15.01.2008  15:20
21.01.2008  14:12
18.01.2008  17:09
21.01.2008  14:19
22.01.2008  16:07
25.01.2008  14:16
21.01.2008  12:30
28.01.2008  08:46
28.01.2008  08:46
28.01.2008  16:40
01.02.2008  13:42
01.02.2008  13:34
02.02.2008  18:33
07.02.2008  11:33
07.02.2008  11:33
07.02.2008  19:39
07.02.2008  23:30
02.02.2008  18:26
04.02.2008  09:31
04.02.2008  09:37
08.02.2008  08:55
11.02.2008  08:26
13.02.2008  08:41
05.05.2008  14:16
05.05.2008  14:07
05.05.2008  14:07
13.05.2008  09:24
14.05.2008  16:47
10.05.2008  20:13
11.05.2008  01:07
14.05.2008  08:38
16.05.2008  10:28
15.05.2008  22:33
18.05.2008  08:48
16.05.2008  20:11
16.05.2008  20:51
17.05.2008  08:58
15.05.2008  11:38
15.05.2008  11:38
23.05.2008  08:58
23.05.2008  08:58
23.05.2008  08:58
23.05.2008  09:56
23.05.2008  08:17
10.06.2008  18:50
0
 

Author Comment

by:pokeeffe
Comment Utility
strange... but if I had another where clause then the query will run as follows:

select TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI:SS')
from  ngsdms60.vw_ph_interface
where TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD/MM/YYYY HH24:MI:SS') >
TO_DATE('01/05/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
and NGTPLNAME = 'CNTIE_CEDEX_MANU';

However remove the last line and run

select TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI:SS')
from  ngsdms60.vw_ph_interface
where TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD/MM/YYYY HH24:MI:SS') >
TO_DATE('01/05/2007 00:00:00', 'DD/MM/YYYY HH24:MI:SS') ;

This query fails!
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Try the below in your test/SIT database :

update your_table
set DATE_TIME_OF_ANALYSIS = replace(DATE_TIME_OF_ANALYSIS,'  ',' ');

The above is to update 2 spaces to 1 space and then try the below query :

select TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI:SS')
from  ngsdms60.vw_ph_interface
where TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI') >
TO_DATE('01.05.2007 00:00', 'DD.MM.YYYY HH24:MI') ;

1) when your data is having a . as the separator, do not use /
2) also when your data is not having seconds stored in the column, do not use SS in the where clause though it may not give a error.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>This query fails!
error message?
0
 

Author Comment

by:pokeeffe
Comment Utility
I have execeuted the replace query on a test environment. Then run the following query:

select DATE_TIME_OF_ANALYSIS
from  ngsdms60.vw_ph_interface
where
TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI') >
TO_DATE('01.05.2005 00:00', 'DD.MM.YYYY HH24:MI');

Error - ORA-01861: literal does not match format string

select DATE_TIME_OF_ANALYSIS
from  ngsdms60.vw_ph_interface
where
TO_DATE(DATE_TIME_OF_ANALYSIS, 'DD.MM.YYYY HH24:MI') >
TO_DATE('01.05.2005 00:00', 'DD.MM.YYYY HH24:MI')
and NGTPLNAME = 'CNTIE_CEDEX_MANU;

works perfectly - (added extra where clause).
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
What happens if your added clause uses NOT equals?
and NGTPLNAME != 'CNTIE_CEDEX_MANU';
0
 

Author Comment

by:pokeeffe
Comment Utility
NGTPLNAME != 'CNTIE_CEDEX_MANU'; Fails with same error... ORA-01861: literal does not match format string

All other records have  NGTPLNAME = 'CNTIE_CEDEX_OTS' and this where clause also works fine so it does not appear to be a problem with the dataset.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Are you saying that there are only two values for NGTPLNAME and the query works when the added clause equals either value?
0
 

Author Comment

by:pokeeffe
Comment Utility
Yes. The following is the full dataset returned from the view for these two columns.

20/06/2007 17:29:00,CNTIE_CEDEX_MANU
20/06/2007 17:11:00,CNTIE_CEDEX_MANU
16/04/2007 10:55:00,CNTIE_CEDEX_OTS
20/06/2007 17:18:00,CNTIE_CEDEX_MANU
28/06/2007 15:43:00,CNTIE_CEDEX_MANU
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
04/07/2007 10:49:00,CNTIE_CEDEX_OTS
20/06/2007 17:18:00,CNTIE_CEDEX_MANU
05/09/2007 11:23:00,CNTIE_CEDEX_MANU
05/09/2007 11:29:00,CNTIE_CEDEX_MANU
28/08/2007 12:22:00,CNTIE_CEDEX_MANU
28/08/2007 14:22:00,CNTIE_CEDEX_MANU
28/08/2007 12:43:00,CNTIE_CEDEX_MANU
04/07/2007 10:27:00,CNTIE_CEDEX_OTS
04/07/2007 10:27:00,CNTIE_CEDEX_OTS
04/07/2007 10:27:00,CNTIE_CEDEX_OTS
04/07/2007 10:49:00,CNTIE_CEDEX_OTS
04/07/2007 10:49:00,CNTIE_CEDEX_OTS
05/09/2007 11:23:00,CNTIE_CEDEX_MANU
06/11/2007 12:30:00,CNTIE_CEDEX_MANU
07/11/2007 11:28:00,CNTIE_CEDEX_MANU
08/11/2007 14:31:00,CNTIE_CEDEX_MANU
06/11/2007 12:30:00,CNTIE_CEDEX_OTS
07/11/2007 11:28:00,CNTIE_CEDEX_OTS
08/11/2007 14:31:00,CNTIE_CEDEX_OTS
09/11/2007 13:27:00,CNTIE_CEDEX_OTS
12/11/2007 10:37:00,CNTIE_CEDEX_OTS
14/11/2007 14:43:00,CNTIE_CEDEX_OTS
06/11/2007 14:19:00,CNTIE_CEDEX_OTS
07/11/2007 11:36:00,CNTIE_CEDEX_OTS
09/11/2007 13:35:00,CNTIE_CEDEX_OTS
12/11/2007 10:44:00,CNTIE_CEDEX_OTS
14/11/2007 14:48:00,CNTIE_CEDEX_OTS
13/11/2007 15:34:00,CNTIE_CEDEX_OTS
16/11/2007 14:16:00,CNTIE_CEDEX_OTS
16/11/2007 10:51:00,CNTIE_CEDEX_OTS
16/11/2007 11:27:00,CNTIE_CEDEX_OTS
16/11/2007 11:33:00,CNTIE_CEDEX_OTS
03/12/2007 09:13:00,CNTIE_CEDEX_OTS
03/12/2007 20:23:00,CNTIE_CEDEX_OTS
04/12/2007 09:05:00,CNTIE_CEDEX_OTS
04/12/2007 20:25:00,CNTIE_CEDEX_OTS
05/12/2007 09:42:00,CNTIE_CEDEX_OTS
05/12/2007 10:05:00,CNTIE_CEDEX_OTS
05/12/2007 20:21:00,CNTIE_CEDEX_OTS
06/12/2007 08:28:00,CNTIE_CEDEX_OTS
07/12/2007 08:00:00,CNTIE_CEDEX_OTS
07/12/2007 08:00:00,CNTIE_CEDEX_OTS
07/12/2007 09:03:00,CNTIE_CEDEX_OTS
09/12/2007 20:32:00,CNTIE_CEDEX_OTS
10/12/2007 20:05:00,CNTIE_CEDEX_OTS
11/12/2007 19:57:00,CNTIE_CEDEX_OTS
01/12/2007 11:30:00,CNTIE_CEDEX_OTS
04/12/2007 09:05:00,CNTIE_CEDEX_OTS
06/12/2007 08:28:00,CNTIE_CEDEX_OTS
05/09/2007 13:04:00,CNTIE_CEDEX_OTS
02/12/2007 20:11:00,CNTIE_CEDEX_OTS
05/12/2007 09:52:00,CNTIE_CEDEX_OTS
07/12/2007 08:07:00,CNTIE_CEDEX_OTS
06/12/2007 20:30:00,CNTIE_CEDEX_OTS
12/09/2007 17:07:00,CNTIE_CEDEX_MANU
05/09/2007 13:04:00,CNTIE_CEDEX_OTS
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
02/12/2007 11:57:00,CNTIE_CEDEX_OTS
03/12/2007 11:04:00,CNTIE_CEDEX_OTS
04/12/2007 08:52:00,CNTIE_CEDEX_OTS
04/12/2007 17:18:00,CNTIE_CEDEX_OTS
04/12/2007 17:18:00,CNTIE_CEDEX_OTS
05/12/2007 09:52:00,CNTIE_CEDEX_OTS
05/12/2007 20:21:00,CNTIE_CEDEX_OTS
06/12/2007 08:34:00,CNTIE_CEDEX_OTS
07/12/2007 08:07:00,CNTIE_CEDEX_OTS
08/12/2007 09:06:00,CNTIE_CEDEX_OTS
10/12/2007 09:19:00,CNTIE_CEDEX_OTS
11/12/2007 10:01:00,CNTIE_CEDEX_OTS
30/11/2007 18:03:00,CNTIE_CEDEX_OTS
02/12/2007 08:52:00,CNTIE_CEDEX_OTS
05/12/2007 09:42:00,CNTIE_CEDEX_OTS
07/12/2007 08:00:00,CNTIE_CEDEX_OTS
01/12/2007 20:28:00,CNTIE_CEDEX_OTS
04/12/2007 17:18:00,CNTIE_CEDEX_OTS
06/12/2007 08:34:00,CNTIE_CEDEX_OTS
05/12/2007 20:21:00,CNTIE_CEDEX_OTS
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
02/07/2007 15:18:00,CNTIE_CEDEX_MANU
14/01/2008 12:02:00,CNTIE_CEDEX_MANU
16/01/2008 13:47:00,CNTIE_CEDEX_MANU
08/01/2008 15:34:00,CNTIE_CEDEX_MANU
23/01/2008 16:21:00,CNTIE_CEDEX_OTS
18/01/2008 17:03:00,CNTIE_CEDEX_OTS
15/01/2008 15:20:00,CNTIE_CEDEX_OTS
21/01/2008 14:12:00,CNTIE_CEDEX_OTS
18/01/2008 17:09:00,CNTIE_CEDEX_OTS
21/01/2008 14:19:00,CNTIE_CEDEX_OTS
22/01/2008 16:07:00,CNTIE_CEDEX_OTS
25/01/2008 14:16:00,CNTIE_CEDEX_OTS
21/01/2008 12:30:00,CNTIE_CEDEX_MANU
28/01/2008 08:46:00,CNTIE_CEDEX_MANU
28/01/2008 08:46:00,CNTIE_CEDEX_MANU
28/01/2008 16:40:00,CNTIE_CEDEX_OTS
01/02/2008 13:42:00,CNTIE_CEDEX_OTS
01/02/2008 13:34:00,CNTIE_CEDEX_OTS
02/02/2008 18:33:00,CNTIE_CEDEX_MANU
07/02/2008 11:33:00,CNTIE_CEDEX_MANU
07/02/2008 11:33:00,CNTIE_CEDEX_MANU
07/02/2008 19:39:00,CNTIE_CEDEX_MANU
07/02/2008 23:30:00,CNTIE_CEDEX_MANU
02/02/2008 18:26:00,CNTIE_CEDEX_MANU
04/02/2008 09:31:00,CNTIE_CEDEX_OTS
04/02/2008 09:37:00,CNTIE_CEDEX_OTS
08/02/2008 08:55:00,CNTIE_CEDEX_OTS
11/02/2008 08:26:00,CNTIE_CEDEX_OTS
13/02/2008 08:41:00,CNTIE_CEDEX_OTS
05/05/2008 14:16:00,CNTIE_CEDEX_MANU
05/05/2008 14:07:00,CNTIE_CEDEX_MANU
05/05/2008 14:07:00,CNTIE_CEDEX_MANU
13/05/2008 09:24:00,CNTIE_CEDEX_MANU
14/05/2008 16:47:00,CNTIE_CEDEX_MANU
10/05/2008 20:13:00,CNTIE_CEDEX_MANU
11/05/2008 01:07:00,CNTIE_CEDEX_MANU
14/05/2008 08:38:00,CNTIE_CEDEX_MANU
16/05/2008 10:28:00,CNTIE_CEDEX_MANU
15/05/2008 22:33:00,CNTIE_CEDEX_MANU
18/05/2008 08:48:00,CNTIE_CEDEX_MANU
16/05/2008 20:11:00,CNTIE_CEDEX_MANU
16/05/2008 20:51:00,CNTIE_CEDEX_MANU
17/05/2008 08:58:00,CNTIE_CEDEX_MANU
15/05/2008 11:38:00,CNTIE_CEDEX_MANU
15/05/2008 11:38:00,CNTIE_CEDEX_MANU
23/05/2008 08:58:00,CNTIE_CEDEX_MANU
23/05/2008 08:58:00,CNTIE_CEDEX_MANU
23/05/2008 08:58:00,CNTIE_CEDEX_MANU
23/05/2008 09:56:00,CNTIE_CEDEX_MANU
23/05/2008 08:17:00,CNTIE_CEDEX_MANU
10/06/2008 18:50:00,CNTIE_CEDEX_MANU
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
can you try without doing any modifications:

select DATE_TIME_OF_ANALYSIS
from  ngsdms60.vw_ph_interface
where  TO_DATE(replace(trim(DATE_TIME_OF_ANALYSIS),' ','')
, 'DD.MM.YYYYHH24:MI') >
TO_DATE('01.05.2005 00:00', 'DD.MM.YYYY HH24:MI')
and NGTPLNAME != 'CNTIE_CEDEX_MANU';

if the abover query still fails, then provide the output of the below query

select DATE_TIME_OF_ANALYSIS, length(date_time_of_analysis) a,
length(trim(date_time_of_analysis)) b,  length(replace(trim(date_time_of_analysis),' ','')) c
from  ngsdms60.vw_ph_interface
where  NGTPLNAME != 'CNTIE_CEDEX_MANU';

Thanks
0
 

Author Comment

by:pokeeffe
Comment Utility
1st query gave the same error ORA-01861: literal does not match format string

Following are the results from the 2nd query:

16.04.2007  10:55,17,17,15
04.07.2007  10:49,17,17,15
04.07.2007  10:27,17,17,15
04.07.2007  10:27,17,17,15
04.07.2007  10:27,17,17,15
04.07.2007  10:49,17,17,15
04.07.2007  10:49,17,17,15
06.11.2007  12:30,17,17,15
07.11.2007  11:28,17,17,15
08.11.2007  14:31,17,17,15
09.11.2007  13:27,17,17,15
12.11.2007  10:37,17,17,15
14.11.2007  14:43,17,17,15
06.11.2007  14:19,17,17,15
07.11.2007  11:36,17,17,15
09.11.2007  13:35,17,17,15
12.11.2007  10:44,17,17,15
14.11.2007  14:48,17,17,15
13.11.2007  15:34,17,17,15
16.11.2007  14:16,17,17,15
16.11.2007  10:51,17,17,15
16.11.2007  11:27,17,17,15
16.11.2007  11:33,17,17,15
03.12.2007  09:13,17,17,15
03.12.2007  20:23,17,17,15
04.12.2007  09:05,17,17,15
04.12.2007  20:25,17,17,15
05.12.2007  09:42,17,17,15
05.12.2007  10:05,17,17,15
05.12.2007  20:21,17,17,15
06.12.2007  08:28,17,17,15
07.12.2007  08:00,17,17,15
07.12.2007  08:00,17,17,15
07.12.2007  09:03,17,17,15
09.12.2007  20:32,17,17,15
10.12.2007  20:05,17,17,15
11.12.2007  19:57,17,17,15
01.12.2007  11:30,17,17,15
04.12.2007  09:05,17,17,15
06.12.2007  08:28,17,17,15
05.09.2007  13:04,17,17,15
02.12.2007  20:11,17,17,15
05.12.2007  09:52,17,17,15
07.12.2007  08:07,17,17,15
06.12.2007  20:30,17,17,15
05.09.2007  13:04,17,17,15
02.12.2007  11:57,17,17,15
03.12.2007  11:04,17,17,15
04.12.2007  08:52,17,17,15
04.12.2007  17:18,17,17,15
04.12.2007  17:18,17,17,15
05.12.2007  09:52,17,17,15
05.12.2007  20:21,17,17,15
06.12.2007  08:34,17,17,15
07.12.2007  08:07,17,17,15
08.12.2007  09:06,17,17,15
10.12.2007  09:19,17,17,15
11.12.2007  10:01,17,17,15
30.11.2007  18:03,17,17,15
02.12.2007  08:52,17,17,15
05.12.2007  09:42,17,17,15
07.12.2007  08:00,17,17,15
01.12.2007  20:28,17,17,15
04.12.2007  17:18,17,17,15
06.12.2007  08:34,17,17,15
05.12.2007  20:21,17,17,15
23.01.2008  16:21,17,17,15
18.01.2008  17:03,17,17,15
15.01.2008  15:20,17,17,15
21.01.2008  14:12,17,17,15
18.01.2008  17:09,17,17,15
21.01.2008  14:19,17,17,15
22.01.2008  16:07,17,17,15
25.01.2008  14:16,17,17,15
28.01.2008  16:40,17,17,15
01.02.2008  13:42,17,17,15
01.02.2008  13:34,17,17,15
04.02.2008  09:31,17,17,15
04.02.2008  09:37,17,17,15
08.02.2008  08:55,17,17,15
11.02.2008  08:26,17,17,15
13.02.2008  08:41,17,17,15
0
 

Author Comment

by:pokeeffe
Comment Utility
Is it possible that this is something to do with the use of a View to filter the underlying dataset. As other dates in this column in the underlying dataset may be inconsistent.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
can you provide the script of this view.. to see what column it is picking and from which table.. i am also thinking in the same lines...

by looking at the output, we are sure everything has 2 spaces and nothing other than that.
0
 

Author Comment

by:pokeeffe
Comment Utility
Here is a modified version of the view script to show the structure of the view. This is modified to hide prioprietry data from a third party database schema.

CREATE OR REPLACE VIEW VW_PH_INTERFACE
(BATCHID, CEDEX_USERNAME, NGTPLNAME, MACHINENAME, SAMPLEID,
 ALGORITHM, DATE_TIME_OF_ANALYSIS, VIABLE_CELL_DENSITY, VIABILITY_PERCENT, TOTAL_CELL_DENSITY,  DILUTION)
AS
SELECT XXXX FROM XXXXX
WHERE NGTPLNAME LIKE 'CNTIE_CEDEX_%'
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
i need to know what is that they have to put in the select list of the view for this column DATE_TIME_OF_ANALYSIS otherwise it is of not much use... probably you can change table names to dummy names in the from clause and also i need only for this column, not for all other columns in the  view
0
 

Author Comment

by:pokeeffe
Comment Utility
The select list is simple select statement on one table... I hope this is clearer...The underlying DATE_TIME is VARCHAR2 and would have many different text values... some of which would not even be in a date_format. Honestly I think this is the issue. If I specify 'CNTIE_CEDEX_MANU or CNTIE_CEDEX_OTS in the where clause all records will have a consistent date format but all others will not conform to this format hence != CNTIE_CEDEX_MANU will not work.

CREATE OR REPLACE VIEW VW_PH_INTERFACE
(BATCHID, CEDEX_USERNAME, NGTPLNAME, MACHINENAME, SAMPLEID,
 ALGORITHM, DATE_TIME_OF_ANALYSIS, VIABLE_CELL_DENSITY, VIABILITY_PERCENT, TOTAL_CELL_DENSITY,  DILUTION)
AS
SELECT BATCHNO, USER, ...., DATE_TIME, ..., DILUTION_RESULT FROM SCHEMA.TABLE1
WHERE NGTPLNAME LIKE 'CNTIE_CEDEX_%'
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 125 total points
Comment Utility
but you said only 'CNTIE_CEDEX_MANU' this works.  we are right in that.

I am ok if you are ok with the query and brings you what you wanted.

If not, then we need to see what are the values present in the DATE_TIME column in that table ....
and probably we need to run some query to dig that out.  Also if view query has NGTPLNAME LIKE 'CNTIE_CEDEX_%' then we can run something like

select date_time, ngtplname, length(date_time) a, length(trim(date_time)) b,
 length(replace(trim(date_time),' ','')) c
from schema.table1
where NGTPLNAME LIKE 'CNTIE_CEDEX_%'
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now