DATE converstion problem, diffrent regional settings

[marcarls]

I have a string with a date format, and I want to convert it into a date using TO_DATE.

Since users can have diffrent regional settings on their computers I dont know in which date format the sting is and I sometimes get the error message "ORA-01830: date format picture ends before converting entire input string" when the string mismatch the format I want to convert it into.

TO_DATE('2002-02-02','YYYY-MM-DD') Works fine
TO_DATE('02/02/2002', 'YYYY-MM-DD') Error (obviously)

Is it possible to write some universal conversion, or do any of you have a suggestion to my problem?

Thanks very much!

Regards Maria

 

[lewisp]

The TO_DATE in your examples wont be affected by the regional settings. You have specified an 'absolute' date format so the input string must be in that format.

TO_DATE will be affected by regional settings if you dont specify a format.

 

[marcarls]

But why do I get the error messages then? When I have set the regional settings on my computer to Sweden, that is I enter a date in my application in format 2002-02-02, it works fine. When I change the region settings to English(United States) the date is entered in the format 02/02/2002 and I get the error message and the conversion of the string will not work?

 

[ija]

TO_DATE is affected by regional settings of the machine it is run on. This usually means the server. That is why you get an error when you convert a date in swedish format on a server with english settings.

You can create a 'mask' using the translate function ([tt]translate(strYourDate, '0123456789', '0000000000')[/tt]) and based on this mask decide (or guess), which regional settings were used on the client machine.

Obviously, you cannot be sure if '02/03/2000' means February 3 or March 2 - but you might be able to create rules like "if the separator is '/' then the month is first" etc.

The only secure way is to store the date from the beginning as datetime column (don't think this is easy) or to store the format mask with the date. Something like this:
'02/03/2000@MM/DD/YYYY'. It is not easy either.

Is there a layer between the database and the user? There could be an universal function for converting dates to standard format (UDTFormat or similar). The point is to move the conversion from the server to the client.

Good luck...

 

[ChrisHunt]

I have written a function that does the job (snipped from a package of useful functions):

/*
* SUPER_TO_DATE
*
* Tries a number of common date formats to convert a string into a date. If
* no conversion can be performed, a null is returned. The calling code may
* optionally suggest a possible format mask - which will be tried first.
*
* Input Parameters : pstrDate   - String containing some kind of date
*                    pstrFormat - Suggested format mask
*
* Return Value     : Date translated into a DATE datatype, or null
*/
FUNCTION super_to_date (pstrDate   IN VARCHAR2,
                        pstrFormat IN VARCHAR2 := NULL) RETURN DATE IS

   -- Define a table of date formats.
   TYPE ltypFormatTable IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
   ltabFormats  ltypFormatTable;

   lnumN        NUMBER;
   ldatAnswer   DATE;
BEGIN
   -- Check for NULL parameters
   IF pstrDate IS NULL THEN
      RETURN NULL;
   END IF;
   
   IF pstrFormat IS NOT NULL THEN
      ltabFormats(0) := SUBSTR(pstrFormat,1,100);
   END IF;
   -- Populate format table
   ltabFormats(1) := 'DD-MON-RR';
   ltabFormats(2) := 'DD/MM/RR';
   ltabFormats(3) := 'DD-MON-YYYY';
   ltabFormats(4) := 'DD/MM/YYYY';
   
   FOR lnumN IN ltabFormats.FIRST..ltabFormats.LAST LOOP
      BEGIN
         ldatAnswer := TO_DATE(pstrDate,ltabFormats(lnumN));
         EXIT;
      EXCEPTION
         WHEN OTHERS THEN
            ldatAnswer := NULL;
      END;
   END LOOP;
   
   RETURN ldatAnswer;
END;

-- Chris Hunt
Extra Connections Ltd

http://www.extracon.com