Convert a String Date into Datetime in Oracle

Convert a string date into datetime in Oracle

Try this:
TO_DATE('2011-07-28T23:54:14Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

Convert string to date in Oracle SQL

Assuming you are using SQL*Plus (or SQL Developer) the default NLS_DATE_FORMAT is applied when a DATE value is displayed. To verify your current format you can run:

select value 
from nls_session_parameters 
where parameter = 'NLS_DATE_FORMAT';

To adjust this, run:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

Then you should see the time as well.

Convert date in string format to datetime format in oracle sql

Lets assume that your value is days past 1899-12-31, then you can use:

INSERT ALL 
INTO TABLE1 (NAME, DATE_COL) VALUES ('ABC' , DATE '1899-12-31' + 44370.4087037037 )
INTO TABLE1 (NAME, DATE_COL) VALUES ('XYZ' , DATE '1899-12-31' + 44370.4087384259 )
SELECT * FROM DUAL;

Then, for your table:

CREATE TABLE table1(
  name VARCHAR2(10),
  date_col DATE
);

The output would be:

SELECT * FROM table1;

Outputs (when NLS_DATE_FORMAT is YYYY-MM-DD HH24:MI:SS):

NAME	DATE_COL
ABC	2021-06-24 09:48:32
XYZ	2021-06-24 09:48:35

PLSQL Convert String to Datetime

Given your date format, you don't want seconds:

select to_date(CLOSED_DATE, 'mm/dd/yyyy hh24:mi') as CLOSED_DATE,
       to_date(SUBMIT_DATE, 'mm/dd/yyyy hh24:mi') as SUBMIT_DATE
from s_daily_ops

(Oracle) convert date string to datetime

It is stored in a column with data type VARCHAR2(100 BYTE).

First of all, you should never ever store DATE/TIMSTAMP as string. It is a database design flaw.

Anyway, you could convert it to TIMESTAMP WITH TIMEZONE.

For example,

SQL> SELECT to_timestamp_tz('2015-06-04T02:58:00.134+08:00',
  2                         'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
  3                         AT TIME ZONE '+8:00' as tm_stamp
  4  FROM dual;

TM_STAMP
-----------------------------------------------------------------
04-JUN-15 02.58.00.134000000 AM +08:00

SQL>

How to Convert String type date to date for entry into oracle DB - Java

OK, so you have a String with the value 2014-11-03 00.00.00.0 and you want to insert that value in a DATE field inside a Oracle Database Table, using Java's JDBC?

Well, first, convert that String into a Date object using a SimpleDateFormat object:

String s = "2014-11-03 00.00.00.0";
Date d = new SimpleDateFormat("yyyy-MM-dd HH.mm.ss.S").parse(s);

Then, if you are using plain statements, reformat it the way Oracle would accept it in an SQL INSERT command, using again a SimpleDateFormat object:

String sd = new SimpleDateFormat("dd/MM/yyyy").format(d);

and insert it into your Statement:

cn.createStatement().executeUpdate("INSERT INTO TABLE(...) VALUES(" + sd + ");

I would recommend you to better use a PreparedStatement instead of a plain SQL INSERT statement so your statement is sanitized:

PreparedStatement ps = cn.prepareStatement("INSERT INTO table VALUES(?, ?, ?)");

And then, insert your date as a Date object without the need of formatting into a String:

ps.setDate([index of the field to insert], d);

how to convert a string date to date format in oracle10g

You can convert a string to a DATE using the TO_DATE function, then reformat the date as another string using TO_CHAR, i.e.:

SELECT TO_CHAR(
         TO_DATE('15/August/2009,4:30 PM'
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')
FROM DUAL;

15-08-2009

For example, if your table name is MYTABLE and the varchar2 column is MYDATESTRING:

SELECT TO_CHAR(
         TO_DATE(MYDATESTRING
                ,'DD/Month/YYYY,HH:MI AM')
       ,'DD-MM-YYYY')
FROM MYTABLE;

---

Related Topics

Find Closest Date in SQL Server

How to Update and Order by Using Ms SQL

Generate Script in SQL Server Management Studio

MySQL Returning the Top 5 of Each Category

How to Pivot Rows into Columns (Custom Pivoting)

Access to Result Sets from Within Stored Procedures Transact-SQL SQL Server

Oracle: Loading a Large Xml File

SQL Grouping by Month and Year

Get Next Sequence Value from Database Using Hibernate

Perform This Hours of Operation Query in Postgresql

How to Use SQL's Getdate() and Dateadd() in a Linq to SQL Expression

SQL - Difference Between Coalesce and Isnull

Create Table with Sequence.Nextval in Oracle

What Does =* Mean

Finding Similar Strings with Postgresql Quickly

How to Create a Temporary Function in Postgresql

Multiple Cte in Single Query

How to Convert a SQL Server 2008 Datetimeoffset to a Datetime

---

Leave a reply

Submit