Link to home
Start Free TrialLog in
Avatar of AbhiJeet
AbhiJeet

asked on

select query - oracle

Hello Experts,
In my table i have a column which contains mix mode data et Dates and strings. The data type of the column is string. So the data looks like:
Column1
21/4/2012
22/3/2015
30/05/2016
ABCD
EFGH
14/05/2001 etc.

Now i want to write the query to select this column which will display the dates in mm/dd/yyyy format and the strings as it is.
Is there any tricky way around?

I have tried all tricks and tweaks with to_date, to_date(...(to_char(...(to_date..))) etc nothing works.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Easiest way is to create your own function to check for the date format.

That said:  None of your sample data matches the 'MM/DD/YYYY' format.  If you want a different format, just change the mask in the function.

create or replace function is_good_date(p_input in varchar2) return number is
	v_date date;
begin
	v_date := to_date(p_input,'MM/DD/YYYY');
	return 1;
	exception when others then return 0;
end;
/

show errors

drop table tab1 purge;
create table tab1(col1 varchar2(20));
	
insert into tab1 values('21/4/2012');
insert into tab1 values('22/3/2015');
insert into tab1 values('30/05/2016');
insert into tab1 values('ABCD');
insert into tab1 values('EFGH');
insert into tab1 values('14/05/2001');

select col1, is_good_date(col1) from tab1;

Open in new window


You could probably come up with a regular expression that would come close but to get 'exact' with leap years, etc... it is difficult.
Try this, I think this should work
 
SELECT CASE
          WHEN INSTR (Column1, '/') > 0
          THEN
             TO_CHAR (TO_DATE (Column1, 'dd/mm/yyyy'), 'mm/dd/yyyy')
          ELSE
             Column1
       END
          C1
  FROM Table1

Open in new window

>>Try this, I think this should work

Until you have '99/99/2012' or 'Hello/World'.
Well his sample data doesnt look like it will contain such values. and Code returns what he wants.

If that is the case then your code will give wrong results when you reach  '1 1 1001' or similar,
it will return a VALID DATE even with no '/' .  and doesnt return what he wants.

Also you can use  ISDATE() for checking for valid dates.
>>Well his sample data doesnt look like it will contain such values. and Code returns what he wants.

It was sort of assumed that the data provided was just a rough sample.  You have to expect more than what sample data was provided.  If the data is straight string data, it likely contains anything and everything.

>>If that is the case then your code will give wrong results when you reach  '1 1 1001' or similar,

Agreed.  We need more about the exact requirements.  It would be a simple matter to adapt my function to look through many different 'allowed' date formats and return values that match any of the 'allowed' ones.

Where you run into problems is month and day formatting:
is 02/03/2001 February 3rd or March 2nd?  If you don't care,, then things get easier.

>>Also you can use  ISDATE() for checking for valid dates.

Not an Oracle function.  The Oracle Topic Area sort of implies an Oracle solution is needed.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Easy and cool, no need to create separate object for this.

Note - Modify columnname, etc as per your need.

SELECT 
	CASE WHEN TRY_PARSE(Column1 AS DATETIME) IS NOT NULL 
                                  THEN TO_CHAR(TO_DATE (Column1,'MM/DD/YYYY')) 
                 ELSE 
                    Column1 
                 END Column1

FROM 
TableName

--

Open in new window


Enjoy !!
Pawan
Pawan,
In the recent weeks, I have had to correct you more than once about the differences between SQL Server and Oracle syntax.

Please stop posting in Oracle questions unless you have tested your syntax against Oracle.

Again:  That isn't even close to valid Oracle syntax...
Ohh, Thanks for correcting. Answering questions in SQL server topic. :)

Please let me just change it for Oracle.
>>Please let me just change it for Oracle.

Make sure it is tested against an Oracle database.
Yes sir. Point Taken. :) Thank you!
Avatar of AbhiJeet

ASKER

hi experts,
The problem is little bigger which i did not realize. Eventually i have to display the records from the table in a grid in my Delphi app.

There are other columns in the table which are actually dates and get displayed in mm/dd/yyyy in UK regions and in dd/mm/yyyy in US regions as per the nls setting i guess.

Hence hardcoding to mm/dd/yyyy format may create consistency issue in display, i guess.

I am including Delphi groups also, if there could be any solution from there. I am writing the query from Delphi file in query component.
Do i need to open a separate post?
@Author - Yes. I think that will attract more Delphi experts.
Sorry but I cannot help with Delphi.

I don't think a second question is necessary.  I don't see how it will attract more Delphi Experts.  When you added the Delhi Topic Area those Experts should get notified about this question.

Back to the question, I hope you have access to their locality.  If so, you should be able to use what I posted as the basis to add the final display information.

I hope the information is stored in the database along with the row.

If not, again, you still have the problem with:
is 02/03/2001 February 3rd or March 2nd?
You have a much bigger problem.
What is this date? 8/9/2012

8th of September 2012 ?
Or
August 9 2012 ?

If you have no input controls on that string column then you CANNOT reliably apply a date format for differeent regions

You might be better off not attempting to convert this bad data and just display it raw.