convert month value 201004 into Apr-2010

General
N/A

convert month value 201004 into Apr-2010

Hi
I have to perform a join on month.

In one table the value stored as 201004 (varchar (10))
and another table value stored as Apr-10 (varchar (20))

I am trying to convert either of the field into matching value but unable to do it
how can I convert 201004 into Apr-10 or Apr-10 into 201004...please help

Thanks in Advance

META
2 REPLIES

Re: convert month value 201004 into Apr-2010

Your problem is that 'Apr-10' means April 1910:

SELECT CAST(CAST('Apr-10' AS DATE FORMAT 'MMM-YY') AS FORMAT 'YYYY/MM/DD');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

'Apr-10'
----------
1910/04/01

So:

SELECT 1 WHERE CAST('Apr-10' AS DATE FORMAT 'MMM-YY') = CAST('201004' AS DATE FORMAT 'YYYYMM');

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

But:

SELECT 1 WHERE CAST('Apr-10' AS DATE FORMAT 'MMM-YY') = CAST('191004' AS DATE FORMAT 'YYYYMM');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

1
----
1

So, you have to process Apr-10 to get Apr-2010:

For example:

SELECT 1 WHERE CAST(oReplace('Apr-10','-','-20') AS DATE FORMAT 'MMM-YY') = CAST('201004' AS DATE FORMAT 'YYYYMM');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

1
----
1

The other way round:

SELECT 1 WHERE CAST('Apr-10' AS DATE FORMAT 'MMM-YY') = CAST(SUBSTR('201004',3) AS DATE FORMAT 'YYMM');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

1
----
1

But here you are changing the real date (2010/04 to 1910/04).

HTH.

Cheers.

Carlos.
N/A

Re: convert month value 201004 into Apr-2010

Carlos

Thank you so much...you are a Guru