DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: date format

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I have a German Customer who is having trouble with date formats, it keeps giving invalid day of the week or invalid month error message.
    I have tried following date formats for him:

    dd- mon - yyyy
    dd-mm-yyyy
    dd-mm-yyyy hh24:mi:ss
    YYYY-MM-DD HH:MI:SS AM
    etc
    none of them work through stored procedure, gives error but when these statements are run thro' application directly the date coversion works.

    CREATE OR REPLACE PROCEDURE pr_authpto
    (inResID Int,inWorkID Int, strStartDate varchar2, strEndDate varchar2)
    IS
    /* Declare Local Variable */
    dtDate date;
    dtStartDate date;
    dtEndDate date;

    Begin
    dtStartDate := to_date(strStartDate,'YYYY-MM-DD "00:00:00.000"');
    dtEndDate := to_date(strEndDate, 'YYYY-MM-DD "00:00:00.000"');

    .... rest of the procedure...
    We pass this date as string and then inside the procedure we convert it to date, this fails.

    Execute pr_authpto (552, 570, '2001-07-22 00:00:00.000', '2001-07-28 00:00:00.000');

    But statements thro' the application which is on web but uses
    odbc works !
    eg:
    SELECT Res_Date_Ext6 FROM mwebRes WHERE Res_ID = 552 and (to_date('2001-07-23 12:00:00 AM','YYYY-MM-DD HH:MI:SS AM') > Res_Date_Ext6 or to_date('2001-07-23 12:00:00 AM','YYYY-MM-DD HH:MI:SS AM') = Res_Date_Ext6) and Res_Date_Ext6 is not null.


    They have oracle server on german windows 2000 machine.
    Here is the output from their v$parameter file:
    NUM NAME TYPE ISDEFAULT ISMODIFIED ISADJ VALUE
    60 nls_language 2 TRUE FALSE FALSE AMERICAN
    61 nls_territory 2 TRUE FALSE FALSE AMERICA
    62 nls_sort 2 TRUE FALSE FALSE
    63 nls_date_language 2 TRUE FALSE FALSE
    64 nls_date_format 2 TRUE FALSE FALSE
    65 nls_currency 2 TRUE FALSE FALSE
    66 nls_numeric_characters 2 TRUE FALSE FALSE
    67 nls_iso_currency 2 TRUE FALSE FALSE
    68 nls_calendar 2 TRUE FALSE FALSE
    69 nls_time_format 2 TRUE FALSE FALSE

    Please any help
    Thanks
    Sonali

    Sonali

  2. #2
    Join Date
    Jun 2000
    Posts
    295
    The format, "00:00:00.000", is wrong.
    Should be: "HH:MI:SS"

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Yes, I have even tried that as I have said, all date format fails thro' procedure.

    thanks
    Sonali
    Sonali

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    what eroror message do you get ???
    AFAIK, you cannot specify anything smaller than a second ...
    try :
    dtStartDate := to_date(strStartDate,'YYYY-MM-DD HH24:MI:SS')
    and send '2001-08-01 10:40:00'

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    He gets invalid day of the week or invalid month error message.
    yes I have tried all those formats, none of them work..
    dd- mon - yyyy
    dd-mm-yyyy
    dd-mm-yyyy hh24:mi:ss
    YYYY-MM-DD HH:MI:SS AM


    Sonali
    Sonali

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    what EXACTLY is the strStartDate you send in ???

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    strStartdate is varchar2 variable so we send that in as a string.

    We convert that to date once it gets inside the procedure, this was designed only to avoid these errors may be due to oracle nls settings.

    Strstartdate format for string matches exactly with to_date conversion inside the procedure. this is where it is failing with error like invalid day of week and invalid month errors.

    This is how this string is passed in
    if you look at my 1st post I had posted both part of stored procedure and a execute statement.
    Another eg, that I have tried

    CREATE OR REPLACE PROCEDURE pr_authpto
    (inResID Int,inWorkID Int, strStartDate varchar2, strEndDate varchar2)
    IS
    /* Declare Local Variable */
    dtDate date;
    dtStartDate date;
    dtEndDate date;

    Begin
    dtStartDate := to_date(strStartDate,'YYYY-mm-dd hh24:mi:ss'
    );
    dtEndDate := to_date(strEndDate, 'YYYY-mm-dd hh24:mi:ss' );

    .... rest of the procedure...
    We pass this date as string and then inside the procedure we convert it to date, this fails.

    Execute pr_authpto (552, 570, '2001-07-22 00:00:00', '2001-07-28 00:00:00');

    Sonali

  8. #8
    Join Date
    Jan 2001
    Posts
    318
    I have additional information today on this from that German customer.

    Note again that he has Oracle 8.1.6.0.0 on german windows 2000 machine.

    Here are few of them that we have tried:

    'YYYY-MM-DD "00:00:00.000"'

    DD-MON-YYYY

    'YYYY-MM-DD HH24:MI:SS'

    'YYYY-MM-DD HH:MI:SS AM'

    'YYYY-MM-DD'

    Using Oracle worksheet these stored procedure works, but none thro' Oracle ODBC test32.
    So may be ODBC driver is a culprit or may be a German server.
    They have Oracle's ODBC driver version 8.01.06.00.

    They didn't change date format for their database, but when making
    SELECT-queries it appears as dd.mm.yy.

    We use similar date formats in sql statements too in our web application.

    One thing which puzzled me is that,
    All of the stored procedures where the date is passed in as a varaiable fail thro' odbc test, our application. But they work if execute is done thro' oracle worksheet.

    Also, the sql statements other than stored procedures which uses similar date formats in our application work thro' application.
    Then why would these statements work and not stored procedure !

    I had also pasted part of their v$parameter file in the 1st post, if that would help.
    Thanks a lot for help
    Sonali







    [Edited by sonaliak on 08-01-2001 at 09:26 AM]
    Sonali

  9. #9
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    if you get invalid day of week, it means that the error does not come from your conversion to date, since you don't use day of week format (d)
    furthermore, there is no reason for your procedure not to work, I guess there is another date manipulation further which produces the error ...

  10. #10
    Join Date
    May 2001
    Location
    Cape Town, South Africa
    Posts
    52
    Have you checked to see if your session's NLS_DATE format is the same as the instance's NLS_DATE format

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width