How to change the date format from MM DD YYYY to YYYY-MM-DD in PL SQL

Question

I have a date column in a table stored as MM DD YYYY format  I have to select and store the same date in another table in YYYY-MM-DD format i e  XSD Date Format  But I am not able to do it  I am using this query   select to date date column  YYYY-MM-DD   from table    But still I am not able to do it   Giving me error      ORA-01843   not a valid month

User · Answer

Basically   Data in a Date column in Oracle can be stored in any user defined format or kept as default  It all depends on NLS parameter   Current format can be seen by   SELECT SYSDATE FROM DUAL    If you try to insert a record and insert statement is NOT in THIS format then it will give   ORA-01843   not a valid month  error  So first change the database date format before insert statements   I am assuming you have bulk load of insert statements  and then execute insert script   Format can be changed by   ALTER SESSION SET nls date format    mm dd yyyy hh24 mi ss     Also You can Change NLS settings from SQL Developer GUI    Tools   preference  database   NLS   Ref  http   oracle ittoolbox com groups technical-functional oracle-sql-l how-to-view-current-date-format-1992815

User · Answer

According to the comments  the data-type in the datatable is DATE  So you should simply use   select date column from table    Now if you execute the select you will get back a date data-type  which should be what you need for the  xsd   Culture-dependent formating of the date should be done in the GUI  most languages have convenient ways to do so   not in the select-statement

User · Answer

if you need to change your column output date format just use to char this well get you a string  not a date

User · Answer

I assume that you can use the Oracle SQL Developer  which you can download from here   You can define the date format which you want to work with   ALTER SESSION SET nls date format  yyyy-mm-dd     With this  now you can perform a query like this   SELECT   FROM emp company WHERE JDate    2014-02-25    If you want to be more specific you can define the date format like this   ALTER SESSION SET nls date format  yyyy-mm-dd hh24 mi ss

User · Answer

select to date to char ORDER DATE  YYYY MM DD     from ORDERS    This might help but  at the end you will get a string not the date  Apparently    your format problem will get solved for sure

User · Answer

It sounds like you ve got it the wrong way round  If your existing data is in MM DD YYYY format  then you want   select to date date column  MM DD YYYY   from table    to convert the existing data to DATE values   I do wonder why they re not stored as dates  to be honest      If you want to perform the conversion in one step  you might want   select to char to date date column  MM DD YYYY     YYYY-MM-DD   from table    In other words  for each row  parse it in MM DD YYYY format  then reformat it to YYYY-MM-DD format    I d still suggest trying to keep data in its  natural  type though  rather than storing it as text in the first place

User · Answer

You can do this simply by    select to char to date date column   MM DD YYYY     YYYY-MM-DD   from table

User · Answer

This worked for me  You can convert to datatype you want be it a date or string  to char TO DATE TO CHAR end date   MM-DD-YYYY    YYYY-MM-DD   AS end date

User · Answer

use  SELECT STR TO DATE date column   Y- m- d   from table    also gothrough   http   dev mysql com doc refman 5 0 en date-and-time-functions html

User · Answer

To convert a DATE column to another format  just use TO CHAR   with the desired format  then convert it back to a DATE type   SELECT TO DATE TO CHAR date column   DD-MM-YYYY     DD-MM-YYYY   from my table

User · Answer

Late reply but for databse-date-type the following line works  SELECT to date t given date  DD MM RRRR   response date FROM Table T  given date s column type is Date

User · Answer

use  select to char date column  YYYY-MM-DD   from table

User · Answer

For military time formatting    select TO CHAR SYSDATE   yyyy-mm-dd hh24 mm ss   from DUAL  --2018-07-10 15 07 15   If you want your date to round DOWN to Month  Day  Hour  Minute  you can try  SELECT TO CHAR  SYSDATE   yyyy-mm-dd hh24 mi ss    full-date  --2018-07-11 10 40 26   TO CHAR  TRUNC SYSDATE   year     yyyy-mm-dd hh24 mi ss    trunc-to-year -- 2018-01-01 00 00 00   TO CHAR  TRUNC SYSDATE   month     yyyy-mm-dd hh24 mi ss    trunc-to-month  -- 2018-07-01 00 00 00   TO CHAR  TRUNC SYSDATE   day     yyyy-mm-dd hh24 mi ss    trunc-to-Sunday  -- 2018-07-08 00 00 00   TO CHAR  TRUNC SYSDATE   dd     yyyy-mm-dd hh24 mi ss    trunc-to-day  -- 2018-07-11 00 00 00   TO CHAR  TRUNC SYSDATE   hh     yyyy-mm-dd hh24 mi ss    trunc-to-hour  -- 2018-07-11 10 00 00   TO CHAR  TRUNC SYSDATE   mi     yyyy-mm-dd hh24 mi ss    trunc-to-minute  -- 2018-07-11 10 40 00 from DUAL   For formats literals  you can find help in  https   docs oracle com cd B28359 01 server 111 b28286 functions242 htm SQLRF52037

User · Answer

SELECT TO DATE TO CHAR date column  MM DD YYYY     YYYY-MM-DD   FROM table

[sql] How to change the date format from MM/DD/YYYY to YYYY-MM-DD in PL/SQL?

Examples related to sql

Examples related to oracle

Examples related to datetime