Type of problem:
- literal does not match format string
- Field content display NA
- View all columns in the table
- ORA-00904: invalid identifier is invalid
- (full) year must be between -4713 and 9999, and not be 0
- Judge whether oracle is not empty
- date format picture ends before converting entire input
Demand: query by using date as criteria
The tables and contents used in the example are as follows:
DROP TABLE "QSACA"."TE_DATE";
CREATE TABLE "QSACA"."TE_DATE" (
"ID" NUMBER VISIBLE NOT NULL ,
"DATESTR" VARCHAR2(50 BYTE) VISIBLE ,
"DATESTR1" DATE VISIBLE
)
TABLESPACE "QSACA"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
-- ----------------------------
-- Records of TE_DATE
-- ----------------------------
INSERT INTO "QSACA"."TE_DATE" VALUES ('1', '2016-01-121', TO_DATE('2016-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('2', '2016-01-14 2', TO_DATE('2022-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('3', '2016-01-21', TO_DATE('2014-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('4', '2016-01-19', TO_DATE('2016-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('5', '2016-01-15', TO_DATE('2018-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('6', 'NA', TO_DATE('2016-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('7', '2016-01-08', TO_DATE('2013-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('8', '2016-01-18', TO_DATE('2016-01-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('9', NULL, TO_DATE('2016-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('10', '2016-01-11', TO_DATE('2019-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "QSACA"."TE_DATE" VALUES ('11', '2016-01-06', TO_DATE('2020-11-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
Table te above_ The DATESTR field type in date is string, and the DATESTR1 field type is date.
The normal writing of sql statements with date as query criteria is as follows:
SELECT * FROM "TE_DATE" WHERE DATESTR1 > TO_DATE('2016-01-19', 'YYYY-MM-DD');
However, if the field type used as query criteria is not a date type, and other types, such as string type (DATESTR), there will be some problems.
If you still use the above sql statement for query:
SELECT * FROM "TE_DATE" WHERE DATESTR1 > TO_DATE('2016-01-19', 'YYYY-MM-DD')
Question 1: ORA-01861: literal does not match format string
An error will be prompted: ORA-01861: literal does not match format string
The text does not match the format string.
reason:
DATESTR1 in the query criteria is a string, which cannot be compared with the date. It needs to be converted to the same type before comparison.
solve:
After converting the format of the fields used in the query criteria to date, you can compare them with the date criteria, as follows:
SELECT * FROM "TE_DATE" WHERE TO_DATE(DATESTR, 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD')
Question 2: ORA-01830: date format picture ends before converting entity input string
Meaning: date formatting ends before converting the entire string.
Personal understanding: when the string is formatted as a date, it ends without converting the whole string, indicating that the length of the string is greater than the "formatted text" used to format the date.
reason:
The length of the field string stored in the data table is greater than the length of the formatted text (YYYY-MM-DD). Such as "2022-01-21 13:49:14".
solve:
We need to intercept the part of the string that matches our formatted date before formatting. As follows:
SELECT * FROM "TE_DATE" WHERE TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');
Question 3: ORA-01840: input value not long enough for date format
Meaning: the length of the input value is less than the length of the date format.
reason:
Looking at the database, we can find that the length of some fields in the data table is not long enough for the characters we want to format.
solve:
We need to filter out strings whose length does not meet the requirements to avoid errors. As follows:
SELECT * FROM "TE_DATE" WHERE LENGTH(datestr) >= 10 AND TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');
Question 4: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Meaning: the year must be between - 4713 and + 9999 and cannot be 0.
reason:
A string with a format other than date exists in the database. As follows:
solve:
We need to filter strings to exclude all strings whose format is not date, as follows:
SELECT * FROM
(
SELECT * from TE_DATE WHERE REGEXP_LIKE ( DATESTR, '^[0-9]{4}-[0-9]{2}-[0-9]{2}' )
)WHERE TO_DATE(SUBSTR(DATESTR, 0, 10), 'YYYY-MM-DD') > TO_DATE('2016-01-19', 'YYYY-MM-DD');
Note: if the date format in the string has 2016:09:12 format, it can also be filtered as follows:
SELECT id FROM DB WHERE REGEXP_LIKE(time, '^[0-9]{4}:[0-9]{2}:[0-9]{2}')
Question 5: ORA-00904: invalid identifier
Meaning: invalid flag.
Question:
There may be several reasons for this problem.
Scenario 1:
There is a syntax problem in the sql statement. Query the attributes that do not exist in the table. For example:
TE_ The A field does not exist in date.
SELECT A FROM "TE_DATE";
ORA-00904: "A": invalid identifier will be prompted.
Scenario 2:
When creating a table, the fields are enclosed in double quotes, such as
create table TTest ( "TESTID" VARCHAR2(36) not null, "NAME" VARCHAR2(36) not null, "ADDRESS" VARCHAR2(36) not null, constraint PK_TEST primary key (TESTID) );
Scenario 3:
There is no defined variable, which is referenced in the following sql statements.
Scenario 4:
When creating a table, the fields are set to lowercase, and an error will be reported when querying.
Oracle should use a larger table when creating tables, including table names and all field names.
Question 6: how to determine whether a field is empty
Judge by is and is not.
Example:
SELECT id, datestr FROM "TE_DATE" WHERE DATESTR IS NOT NULL
Question 7: what is the NA value in the data table
NA = not applicable, or = not available, which can be explained unnecessarily and unnecessarily.
This means that this data does not need to be entered or blank.
How do we filter NA data?
SELECT * FROM TBL_RYXW WHERE CRBM != 'NA'
Previous: Oracle (V) Oracle sequence, view and index Next: to be determined