date_added is a date column in weather data table. It stores data as 1/30/2002 10:00:00 PM format
I need to select data from 1/1/2013 00:00:00 AM until 1/1/2013 00:00:00 AM. I am getting "ORA-01722: invalid number" error. Please advise.
here is the table definition
CREATE TABLE MWATCH.WEATHER_DATA_NCIS
(
WEATHER_SAN NUMBER(10),
LOCATION_SAN NUMBER(10),
WEATHER_DATETIME DATE,
TEMP NUMBER(3),
HUMIDITY NUMBER(3),
WIND_SPEED NUMBER(3),
SKY VARCHAR2(1 BYTE),
DATE_ADDED DATE,
ADDED_BY VARCHAR2(15 BYTE)
);
I am getting the error when I do the following select
select * from weather_data
WHERE to_char(date_added,'dd/mm/yyyy hh:mi:ss AM')
BETWEEN to_char('1/1/2013 00:00:00 AM','dd/mm/yyyy hh:mi:ss AM')
AND to_char('1/1/2013 00:00:00 AM'','dd/mm/yyyy hh:mi:ss AM');
Error at line 4
ORA-01722: invalid number