0

I want to convert a particular date to sysdate format in SQL developer.

The date I have is : 2020-05-04 00:00:00.0

The date I want is : 04-MAY-20

I have tried using to_date and trunc but I'm not able to figure out how it would work for me.

HackersInside
  • 307
  • 2
  • 6
  • 17
  • 1
    There's no thing as sysdate format. Dates are abstract representations of time, they get a format when you convert them to text. And `to_date` converts *to* date (which already is), you want `to_char`. – Álvaro González May 04 '20 at 15:37
  • Hi i can't set a custom date time format, as this relates to my official work. Is there a way I can convert it to the format I want. – HackersInside May 04 '20 at 15:43
  • 2
    @thatjeffsmith - while the basis of your recommendation is spot-on, I'm really surprised at your recommending using the 'RR' mask. Much better if he is taught to abandon 2-digit years. Especially 2-digit years based on a format mask that was supposed to be a temporoary band-aid in dealing with Y2k. – EdStevens May 04 '20 at 16:37
  • 1
    I wasn't asked what the better date format would be, they showed a 2 digit date, hence the RR or i guess i could have said YY – thatjeffsmith May 04 '20 at 16:39
  • @thatjeffsmith - I guess Ed's point is this: If a stranger asks you where to find a concrete wall so they can bang their head against it, you might ask them whether they are sure that's a good idea, rather than just pointing them to a concrete wall. Showing someone what they asked for may not always be the best choice. –  May 04 '20 at 23:34
  • dates are stored as dates...there's no format. format only applies as you view the data in your sql query results. so query your date value as select to_char(your_date_col, 'DD-MON-YYYY') from table. Best practice is to use 4 digit years not 2 digit years, if my birthday is 1919, and you show me as '1-MAY-19', you're going to think I'm a baby vs an octogenarian. – thatjeffsmith May 05 '20 at 15:15

0 Answers0