logo
down
shadow

Inserting Date gives error ORA-01861: literal does not match format string


Inserting Date gives error ORA-01861: literal does not match format string

By : luisalfredo.narvaez
Date : October 14 2020, 08:10 PM
To fix the issue you can do Insert date into WSH_Delivery_Details_Interface (https://docs.oracle.com/cloud/r13_update17c/scmcs_gs/OEDSC/WSH_DELIVERY_DETAILS_tbl.htm) throws this error , The error can be reduced to:
code :
select TO_DATE('11/12/2018T05:10:30-00:00','DD/MM/YYYY ') from dual;

Error report -
ORA-01861: literal does not match format string
select TO_DATE('11/12/2018T05:10:30-00:00','DD/MM/YYYY"T"HH24:MI:SS"-00:00"') from dual;

TO_DATE('11/12/2018
-------------------
2018-12-11 05:10:30
select TO_TIMESTAMP_TZ('11/12/2018T05:10:30-00:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM')
from dual;

TO_TIMESTAMP_TZ('11/12/20
-------------------------
2018-12-11 05:10:30.0 GMT
select TO_TIMESTAMP_TZ('11/12/2018T04:10:30-01:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM') as orig,
  SYS_EXTRACT_UTC(
    TO_TIMESTAMP_TZ('11/12/2018T04:10:30-01:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM')) as utc
from dual;

ORIG                         UTC                  
---------------------------- ---------------------
2018-12-11 04:10:30.0 -01:00 2018-12-11 05:10:30.0


Share : facebook icon twitter icon
ORA-01861: literal does not match format string 'date datatype error'

ORA-01861: literal does not match format string 'date datatype error'


By : Babu Mehra
Date : March 29 2020, 07:55 AM
this one helps. When you supply a "plain" string the value is converted using implicit type conversion based on your NLS setting. A (standard) date literal needs the keyword date followed by a string formatted in ISO style (yyyy-mm-dd):
code :
insert into Coordinate_with 
  (cor_id, cmp_id, start_date, shifted)
values 
  ('COR-01','CMP-02', DATE '2009-03-22','Morning');
insert into Coordinate_with 
  (cor_id, cmp_id, start_date, shifted)
values 
  ('COR-01','CMP-02', to_date('2009-03-22', 'yyyy-mm-dd','Morning');
SQL Error: ORA-01861: literal does not match format string 01861

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


By : user7622880
Date : March 29 2020, 07:55 AM
should help you out Try replacing the string literal for date '1989-12-09' with TO_DATE('1989-12-09','YYYY-MM-DD')
"ORA-01861: literal does not match format string" error in pl/sql code while inserting a date argument

"ORA-01861: literal does not match format string" error in pl/sql code while inserting a date argument


By : DanielleP
Date : March 29 2020, 07:55 AM
should help you out you are trying to handle invalid date format in the wrong way
try using a separate try catch block around your pdate variable
code :
declare
   invalid_date varchar2(10) := '201401';
   d date;
begin
   begin
           d := to_date(invalid_date,'yyyymmdd');
   exception when others then
           dbms_output.put_line('Handling invalid date');
   end;
end;
ORA-01861: literal does not match format string when parsing XML date

ORA-01861: literal does not match format string when parsing XML date


By : Anton Yap
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I am trying to parse this type of XML date to Oracle, but it does not work: , You can try this way:
code :
SELECT to_date('2010-09-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') from dual;
SELECT to_char('2010-09-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') from dual;
JDBC - ORA-01861: literal does not match format string 01861

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


By : Swati Mittal
Date : March 29 2020, 07:55 AM
To fix this issue Never pass DATE or TIMESTAMP values as as Strings.
Use a PreparedStatement and pass an instance of java.sql.Date or java.sql.Timestamp
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org