473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Hateful Date Formatting in Oracle/ASP

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
Jul 19 '05 #1
7 3848
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" <an*******@discussions.microsoft.com> wrote in message
news:28*****************************@phx.gbl...
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

Jul 19 '05 #2
-----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 eitheris 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


Jul 19 '05 #3
> 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.)
Jul 19 '05 #4
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]" <te*****@dnartreb.noraa> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.)

Jul 19 '05 #5
> 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
Jul 19 '05 #6
CJM
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
Jul 19 '05 #7
> 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.)
Jul 19 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Rick | last post by:
I am going crazy on how to format a simple date. I have this in my MySQL table. "2005-03-10 08:44:21" and I want it to format out like "3/10/2005" or "03/10/05" but I seem to be going at it the...
7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
0
by: Brian Conway | last post by:
I am having some validation and insertion problems. I am using a date picker that takes the selected date and puts it to ("dd-MMM-yyyy") format, as this was the only format that Oracle would...
1
by: Serge Poirier | last post by:
Good Day Folks, I'm displaying a date field from an Oracle table in a datagrid with the following template. <ItemTemplate> <asp:Label id="hiredate" runat="server" Text='<%# DataBinder.Eval...
1
by: Nathan Sokalski | last post by:
I have a field in a dataset (which I filled using an Oracle database) that contains a date. I want to format the date to look like the ToLongDateString() method. The current code that I have in my...
4
by: Des | last post by:
I am displaying records from a table including a stored date. When display $row, i get the obvious 2006-05-21. However this is not what the guy wants. He wants 21-May-2006. I have tried several...
14
by: peteh | last post by:
Hi All; We have many production jobs that "load from cursor" to a UDB/AIX 8.2 (with dpf) data warehouse from source tables residing Oracle 9i. Since Oracle dates are (roughly) equivalent to DB2...
7
by: Middletree | last post by:
I am trying to display dates in a spreadsheet, but the dates need to be in a format that will allow them to be sorted in Excel. The datatype in the SQL Server database is datetime. In this case, I...
2
by: hdogg | last post by:
Here is my situation: In the "PERIOD" column of my oracle database it is MM/DD/YY, ie 10/07/02. I need to convert that to a different oracle date format so i can filter and sort it. What can i...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.