CODEWITHSUNDEEP
mysqlsqldatabaseoracle-databaseto-date
Shubham Sharan
Shubham Sharan

Reputation: 23

TO_DATE to YYYYMMDD from DD-MON-YY

I have some data in the following date format. '27-SEP-97' i.e DD-MON-YY

Now I want to convert this to YYYYMMDD. I am using the following script to convert this. 

TO_CHAR(TO_DATE(CHD_DATE_FIRST_ACT,'DD-MON-YY'),'YYYYMMDD')

but this is giving me the following output. 

20970927

I want this data to be in YYYYMMDD format, such that the output looks like this- 19970927

Upvotes: 0

Views: 8982

Answers (3)

HubballiHuli
HubballiHuli

Reputation: 777

You can make use of mysql replace and str_to_date functions
replace takes 3 arguments as show below
replace(string,find_string,replace_with)
you should simply replace - with a blank string
if your str is '27-SEP-97' to get the output as 19970927 execute the following in mysql

select replace(str_to_date('27-SEP-97','%d-%b-%Y'),'-','') as date;
output 

+----------+
| date     |
+----------+
| 19970927 |
+----------+

%b is used to mention abbreviated month name

click the below links to know more about mysql date and time functions
Mysql Data and Time functions

Upvotes: 0

Ganesh
Ganesh

Reputation: 506

--USE RR instead of YY in your query.

select TO_CHAR(TO_DATE('27-SEP-97','DD-MON-RR'),'YYYYMMDD') from dual;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

If '27-SEP-97' is a string (which is what your words suggest), then such a combination of TO_this and TO_that might do the job:

SQL> with test as (select '27-SEP-97' datum from dual)
  2  select to_char(to_date(datum, 'dd-mon-rr', 'nls_date_language = english'), 'yyyymmdd') result
  3  from test;

RESULT
--------------------------------------------------------------------------------
19970927

SQL>

Upvotes: 4

Related Questions