Hateful Date Formatting in Oracle/ASP

Discussion in 'ASP General' started by Colin Steadman, Jul 8, 2004.

  1. I'm returning a date (5th Jan 2004) from Oracle using the
    following query:

    select TO_CHAR(invoice_date,'DD/MM/YYYY') from...

    This should return my date in the UK format, and it
    certainly appears to be doing just that in Toad. And it
    also looks correct in my form as it shows as:

    05/01/2004

    However when I submit my form I am having to convert the
    date to an Oracle format using this function:

    Function ConvertToOracleDate(dtmDate)
    If IsDate(dtmDate) Then
    Dim arData(2)
    arData(0) = Right("0" & Day(dtmDate),2)
    arData(1) = UCase(MonthName(Month(dtmDate),True))
    arData(2) = Year(dtmDate)
    ConvertToOracleDate = Join(arData," ")
    End If
    End Function

    And when I do this the date is being interpreted
    incorrectly and is converted to:

    01 MAY 2004

    How is happening?

    TIA,

    Colin
    Colin Steadman, Jul 8, 2004
    #1
    1. Advertising

  2. Some part of your system is set up as US regional settings.

    Why not use a date format that is going to avoid these problems altogether?
    I'm not sure whether YYYYMMDD or YYYY-MM-DD is safer in Oracle, but either
    is certainly safer than the ambiguous d/m/y or m/d/y formats.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)





    "Colin Steadman" <> wrote in message
    news:28b4801c464b9$6b176840$...
    > I'm returning a date (5th Jan 2004) from Oracle using the
    > following query:
    >
    > select TO_CHAR(invoice_date,'DD/MM/YYYY') from...
    >
    > This should return my date in the UK format, and it
    > certainly appears to be doing just that in Toad. And it
    > also looks correct in my form as it shows as:
    >
    > 05/01/2004
    >
    > However when I submit my form I am having to convert the
    > date to an Oracle format using this function:
    >
    > Function ConvertToOracleDate(dtmDate)
    > If IsDate(dtmDate) Then
    > Dim arData(2)
    > arData(0) = Right("0" & Day(dtmDate),2)
    > arData(1) = UCase(MonthName(Month(dtmDate),True))
    > arData(2) = Year(dtmDate)
    > ConvertToOracleDate = Join(arData," ")
    > End If
    > End Function
    >
    > And when I do this the date is being interpreted
    > incorrectly and is converted to:
    >
    > 01 MAY 2004
    >
    > How is happening?
    >
    > TIA,
    >
    > Colin
    Aaron [SQL Server MVP], Jul 8, 2004
    #2
    1. Advertising


  3. >-----Original Message-----
    >Some part of your system is set up as US regional

    settings.
    >


    Any idea where this might be? Would it be in IIS
    somewhere or Oracle?


    >Why not use a date format that is going to avoid these

    problems altogether?
    >I'm not sure whether YYYYMMDD or YYYY-MM-DD is safer in

    Oracle, but either
    >is certainly safer than the ambiguous d/m/y or m/d/y

    formats.

    I'll give as I'm getting quite desparte here! Its been
    doing some really bizarre things, just now I got:

    04 JUL 2015 returned from 15/07/04

    Its driving me up the wall.

    Thank you.
    Colin
    Colin Steadman, Jul 8, 2004
    #3
  4. > Any idea where this might be? Would it be in IIS
    > somewhere or Oracle?


    This is probably at the operating system level. But fixing this is not
    going to solve the problem. When someone logs into the box, the regional
    settings might change. And someone else might change them back on you.
    Hell, a central server might be going out and adjusting the settings on all
    the boxes so they match, once a night.

    > 04 JUL 2015 returned from 15/07/04


    Well, no kidding! What kind of input is that? I could come up with six
    different dates for that "date" you sent in. The operating system is going
    to be more restrictive, of course, but it's not a mindreader, and it's
    probably going to guess wrong. At least if you used a 4-digit year (do we
    all remember the Y2K problem???), I'd only have two possibilities.

    Once again, provide unambiguous input, and you will get unambiguous output!
    If you are allowing users to enter dates in a freetext form field, STOP
    DOING THAT! NOW! Use a calendar control, or separate dropdowns -- and
    VALIDATE before submission!

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Jul 8, 2004
    #4
  5. Colin Steadman

    Mark Schupp Guest

    I finally broke down and replaced all the date fields on our forms with a
    popup date picker (there are a number of scripts out there if a search is
    done). Now all forms send dates in yyyy-mm-ddThh:mm:ss format automatically.
    The original poster might consider that.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Aaron [SQL Server MVP]" <> wrote in message
    news:%...
    > > Any idea where this might be? Would it be in IIS
    > > somewhere or Oracle?

    >
    > This is probably at the operating system level. But fixing this is not
    > going to solve the problem. When someone logs into the box, the regional
    > settings might change. And someone else might change them back on you.
    > Hell, a central server might be going out and adjusting the settings on

    all
    > the boxes so they match, once a night.
    >
    > > 04 JUL 2015 returned from 15/07/04

    >
    > Well, no kidding! What kind of input is that? I could come up with six
    > different dates for that "date" you sent in. The operating system is

    going
    > to be more restrictive, of course, but it's not a mindreader, and it's
    > probably going to guess wrong. At least if you used a 4-digit year (do we
    > all remember the Y2K problem???), I'd only have two possibilities.
    >
    > Once again, provide unambiguous input, and you will get unambiguous

    output!
    > If you are allowing users to enter dates in a freetext form field, STOP
    > DOING THAT! NOW! Use a calendar control, or separate dropdowns -- and
    > VALIDATE before submission!
    >
    > --
    > http://www.aspfaq.com/
    > (Reverse address to reply.)
    >
    >
    Mark Schupp, Jul 8, 2004
    #5
  6. > This is probably at the operating system level. But fixing this is not
    > going to solve the problem. When someone logs into the box, the regional
    > settings might change. And someone else might change them back on you.
    > Hell, a central server might be going out and adjusting the settings on all
    > the boxes so they match, once a night.
    >
    > > 04 JUL 2015 returned from 15/07/04

    >
    > Well, no kidding! What kind of input is that? I could come up with six
    > different dates for that "date" you sent in. The operating system is going
    > to be more restrictive, of course, but it's not a mindreader, and it's
    > probably going to guess wrong. At least if you used a 4-digit year (do we
    > all remember the Y2K problem???), I'd only have two possibilities.



    Well it certainly is guessing wrong. I really hate dealing with dates
    in ASP, it seems to fight me on them. I've given up for now and come
    up with the function copied at the bottom of this post. Its probably
    a very inappropriate and cumbersome way of solving the problem, but I
    did not have a good day yesterday and I just want a solution now.
    This seems to work.


    > Once again, provide unambiguous input, and you will get unambiguous output!
    > If you are allowing users to enter dates in a freetext form field, STOP
    > DOING THAT! NOW! Use a calendar control, or separate dropdowns -- and
    > VALIDATE before submission!


    Alass, I did actually setup the form with nice drop-down boxes for
    each date element. These were automatically generated from code and
    it looked good and worked well.. However the users didn't like it,
    for speed they prefer to type in the date so I was asked to provide a
    simple inputbox instead. I pointed out that this could be ambiguous,
    but was overruled.

    Heres the function I came up with in desparation:


    Function ConvertToOracleDate(dtmDate)

    Dim i
    Dim firstSeparator
    Dim secondSeparator

    For i = 1 To CInt(Len(dtmDate))
    If Not IsNumeric(mid(dtmDate,i,1)) Then
    firstSeparator = i
    Exit For
    End If
    Next

    For i = firstSeparator + 1 To CInt(Len(dtmDate))
    If Not IsNumeric(mid(dtmDate,i,1)) Then
    secondSeparator = i
    Exit For
    End If
    Next

    'build date
    Dim arData(2)
    arData(0) = Right("0" & Mid(dtmDate,1,firstSeparator-1),2)
    arData(1) = UCase(MonthName(Right("0" &
    Mid(dtmDate,firstSeparator+1,secondSeparator-firstSeparator-1),2),True))
    arData(2) = Right(dtmDate,Len(dtmDate)-secondSeparator)

    ConvertToOracleDate = Join(arData," ")
    Erase arData

    Set i = Nothing
    Dim firstSeparator = Nothing
    Dim secondSeparator = Nothing

    End Function
    Colin Steadman, Jul 9, 2004
    #6
  7. Colin Steadman

    CJM Guest

    Colin,

    I've been in a similar situation too, but you can afford to accept ambiguous
    dates.

    One alternative solution is to use a format like 09 Jul 2004... You can the
    transpose that into an internal date format (eg yyyymmdd or whatever).

    The key thing is to publish the date formats you accept to the user, and
    refuse to accept any that dont conform to your preferred standard.

    Chris
    CJM, Jul 9, 2004
    #7
  8. > However the users didn't like it,
    > for speed they prefer to type in the date


    What about a calendar popup? Surely this would be faster than typing the
    date in...

    > I pointed out that this could be ambiguous,
    > but was overruled.


    So what prevents you from using client-side script to validate the input,
    force YYYYMMDD, and then ask them which method they prefer?

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Jul 9, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    594
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,243
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    511
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    718
    Richard Heathfield
    Nov 8, 2003
  5. Feyruz
    Replies:
    4
    Views:
    2,155
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page