• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2303
  • Last Modified:

Use of TO_DATE function in Where Clause.

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
pokeeffe
Asked:
pokeeffe
  • 18
  • 15
  • 5
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Tomas Helgi JohannssonCommented:
    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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pokeeffeAuthor Commented:
Same error with all of the above queries...

ORA-01861: literal does not match format string
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
kretzschmarCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
kretzschmarCommented:
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
 
pokeeffeAuthor Commented:
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
 
pokeeffeAuthor Commented:
Lenght (date_time_of_analysis) returns 17, above is 16 ??
0
 
pokeeffeAuthor Commented:
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
 
kretzschmarCommented:
what kind of type is your date_time_of_analysis-Field?
0
 
pokeeffeAuthor Commented:
VARCHAR2(1024)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
kretzschmarCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
kretzschmarCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
Yes nav_kum_v I did try this and still the same error... I cut and paste your query exactly... very bizarre!
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
The output is as follows:

Length, Count(1)
17, 137
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>This query fails!
error message?
0
 
pokeeffeAuthor Commented:
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
 
awking00Commented:
What happens if your added clause uses NOT equals?
and NGTPLNAME != 'CNTIE_CEDEX_MANU';
0
 
pokeeffeAuthor Commented:
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
 
awking00Commented:
Are you saying that there are only two values for NGTPLNAME and the query works when the added clause equals either value?
0
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
pokeeffeAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 18
  • 15
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now