CODEWITHSUNDEEP
sqloracle-databasedate
Maestro
Maestro

Reputation: 69

How to use the to_char funtion with different formats based on condition

I want to know how I can use the to_char query in SQL.

Name    Gender  Salary  Start_Date
------- ------- ------- ----------
Sally   Female  45000   23/02/2001
Frank   Male    23200   15/12/2008

The start_date is in the format DD/MM/YYYY, from my understanding the to_char function allows us to view dates in other formats. How do I use it to show MM/DD/YYYY for Frank only?

Upvotes: 0

Views: 93

Answers (4)

ADH - THE TECHIE GUY
ADH - THE TECHIE GUY

Reputation: 4373

with select statement,

SELECT col-name1,col-name2, TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI:SS') FROM table-name;

Upvotes: 0

Анатолий Предеин
Анатолий Предеин

Reputation: 571

try decode (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm)

with a(name,start_date) as (
           select 'Sally' , to_date('23/02/2001','dd/mm/yyyy') from dual 
             union all
           select 'Frank' , to_date('15/12/2008','dd/mm/yyyy') from dual )
select name,
       decode(name,'Frank',to_char(start_date,'mm/dd/yyyy'),to_char(start_date,'dd/mm/yyyy')) start_date
  from a

output:

NAME  START_DATE
----- ----------
Sally 23/02/2001 
Frank 12/15/2008

Upvotes: 0

kedar kamthe
kedar kamthe

Reputation: 8178

Use Decode 

SELECT name,
   gender,
   salary,
   Decode(name,'Frank',TO_CHAR( start_date, 'MM/DD/YYYY' ),
          TO_CHAR( start_date, 'DD/MM/YYYY' )
   END AS start_date  FROM   your_table;

Upvotes: 0

MT0
MT0

Reputation: 168061

How do I use it to show MM/DD/YYYY for Frank only?

Use a CASE statement:

SELECT name,
       gender,
       salary,
       CASE name
         WHEN 'Frank'
         THEN TO_CHAR( start_date, 'MM/DD/YYYY' )
         ELSE TO_CHAR( start_date, 'DD/MM/YYYY' )
       END AS start_date
FROM   your_table;

Upvotes: 4

Related Questions