Date format in oracle

Posted on 2011-05-12
Medium Priority
Last Modified: 2012-06-27

I have a date field in the format of dd-mon-yy(01-MAY-11) and I want to convert it to yyyy-mm(2011-05).
Thanks in advance.
Question by:mrong
  • 4
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 35747215
dates don't have formats,  only strings do

to convert a date use


LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35747219
If it is a date: to_char('YYYY-MM')
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35747222
is the field date or varchar?

if it's date:
TO_CHAR(yourfield, 'YYYY-MM')

if it's varchar:
TO_CHAR( TO_DATE(yourfield, 'DD-MON-YY'), 'YYYY-MM')
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

LVL 74

Expert Comment

ID: 35747223
if your starting value is actually a string then first, convert the string to a date then convert that date to the final string format


LVL 74

Expert Comment

ID: 35747236
one last thing,  your starting format has YY

assuming it's a string then

I suggest using YYYY,  no need to reinvent the y2k bug
LVL 74

Expert Comment

ID: 35747327

I appreciate the points, but given 3 essentially simultaneous, identical answers,
 a split would be in order

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question