Home » SQL & PL/SQL » SQL & PL/SQL » Convert Short Date to Date (10g)
Convert Short Date to Date [message #299632] Tue, 12 February 2008 15:57 Go to next message
amardilo
Messages: 37
Registered: February 2007
Member
Hi there.

I can't find a way to convert a user entered date (i.e. 31/01/2007 or 31-01-2007) to a valid Date in Oracle?

I need to use the user input to search against a date column.

Does anyone know a way to do this?
Re: Convert Short Date to Date [message #299633 is a reply to message #299632] Tue, 12 February 2008 15:59 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Use the to_date function.
Re: Convert Short Date to Date [message #299635 is a reply to message #299633] Tue, 12 February 2008 16:03 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
I tried:

SELECT TO_DATE('31/01/2007') FROM DUAL


but I got back an error message saying not a valid month. If I change the 01 to JAN it works fine.
Re: Convert Short Date to Date [message #299636 is a reply to message #299632] Tue, 12 February 2008 16:04 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Google to_date and you'll find some great examples.
Re: Convert Short Date to Date [message #299641 is a reply to message #299636] Tue, 12 February 2008 16:29 Go to previous messageGo to next message
amardilo
Messages: 37
Registered: February 2007
Member
TLegend33 wrote on Tue, 12 February 2008 16:04
Google to_date and you'll find some great examples.


Thanks I should have done that first.
Re: Convert Short Date to Date [message #299729 is a reply to message #299641] Wed, 13 February 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you should have read the documentation: TO_DATE and see that TO_DATE can take a format parameter.

Regards
Michel
Re: Convert Short Date to Date [message #311968 is a reply to message #299632] Mon, 07 April 2008 10:09 Go to previous messageGo to next message
imen_mr2004
Messages: 22
Registered: October 2006
Location: tunisia
Junior Member
just try
to_char(date_variable,'dd-mm-yyyy')
and if u can send me your instruction and i'll work in it to u.
good luck
Re: Convert Short Date to Date [message #311981 is a reply to message #311968] Mon, 07 April 2008 10:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
to_char does NOT convert an entered string to a valid date.
Re: Convert Short Date to Date [message #312032 is a reply to message #299632] Mon, 07 April 2008 19:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I recall some years ago, someone wrote some kind of "intelligent date conversion" function, anyone remember it or better yet, have a copy?

Kevin
Re: Convert Short Date to Date [message #312259 is a reply to message #312032] Tue, 08 April 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean a function that can take any string and convert it into a date?
I wonder how it can say if '01/02/08', '02/01/08' '08/02/01', '08/01/02 are same dates or not?

Regards
Michel
Re: Convert Short Date to Date [message #312301 is a reply to message #299632] Tue, 08 April 2008 08:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you got me. I just remember someone has such a thing. For sure there were situations that can't be accounted for. Maybe it just tossed an error for those. But I am pretty sure there was such a thing.

Kevin
Re: Convert Short Date to Date [message #312306 is a reply to message #312301] Tue, 08 April 2008 08:51 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I have something in that direction in production :

REATE OR REPLACE FUNCTION guess_date( INCHAR IN VARCHAR ) RETURN date AS

  v_temp_date date;

BEGIN

  BEGIN 
      v_temp_date := To_date(INCHAR,'dd.mm.yyyy');
  RETURN v_temp_date;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;

  BEGIN 
      v_temp_date := To_date(INCHAR,'dd.mm.yy');
  RETURN v_temp_date;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;

  BEGIN 
      v_temp_date := To_date(INCHAR,'ddmmyyyy');
  RETURN v_temp_date;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;

  BEGIN 
      v_temp_date := To_date(INCHAR,'ddmmyy');
  RETURN v_temp_date;
  EXCEPTION
    WHEN OTHERS THEN NULL;
  END;
  
  RETURN to_date('01.01.1900','dd.mm.yyyy');

END;
/


Where I simply try all the different formats of dates that seemed to be arriving at that particular point in data flow, and return a valid SQLwise but invalid datawise "flag" date in the event the string isn't recognized.

Then the invalid data ends up in a "to be checked manually" pool of the application without breaking the processing itself.
Re: Convert Short Date to Date [message #312310 is a reply to message #312306] Tue, 08 April 2008 09:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
But as Michel says, what if you were to pass the value of '01.02.01' is that 1st Feb or 2nd Jan (or something different)
Re: Convert Short Date to Date [message #312312 is a reply to message #312310] Tue, 08 April 2008 09:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then the guessed date would be wrong.

This is not intended to be used everywhere to catch every possible entered date, but only at points where you expect a certain fixed number of different possible date formats to arrive from external data in situations where it is OK to be right 99% of the time. Wink

Re: Convert Short Date to Date [message #312314 is a reply to message #312312] Tue, 08 April 2008 09:38 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
In that case, you might find something like:
with t as (select '01-02-2007' str from dual union all
           select '7-8-2007' str from dual union all
           select '2007/03/28' str from dual union all
           select '10-MAY-07' str from dual)
           select str, to_date(str, case when regexp_like(str,'[0-9]{1,2}-[0-9]{1,2}-[0-9]{4}') then 'dd-mm-yyyy'
                                         when regexp_like(str,'[0-9]{4}/[0-9]{1,2}/[0-9]{1,2}') then 'yyyy/mm/dd'
                                         when regexp_like(str,'[0-9]{2}-[A-Z]{3}-[0-9]{2}') then 'dd-mon-yy' end) 
           from t

useful (although I think that 99% is a bit optimistic Wink

It should be noted that I basically nicked this from someone over at OTN. I'm sorry but I can't find the original 'perpetrator' to reference.

[Updated on: Tue, 08 April 2008 09:39]

Report message to a moderator

Previous Topic: Materialized view refresh
Next Topic: create user
Goto Forum:
  


Current Time: Sun Dec 04 23:06:45 CST 2016

Total time taken to generate the page: 0.19562 seconds