?
Solved

Date format in oracle

Posted on 2011-05-12
6
Medium Priority
?
236 Views
Last Modified: 2012-06-27
Greeting,

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.
0
Comment
Question by:mrong
  • 4
6 Comments
 
LVL 74

Accepted Solution

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

to convert a date use

to_char(your_date,'YYYY-MM')

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35747219
If it is a date: to_char('YYYY-MM')
0
 
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')
0
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

by:sdstuber
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


to_char(TO_DATE(your_string,'DD-MON-YY'),'YYYY-MM')

0
 
LVL 74

Expert Comment

by:sdstuber
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35747327
mrong,

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

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