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 ConvertToOracle Date(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)
ConvertToOracle Date = 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 7 3882
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*******@disc ussions.microso ft.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 ConvertToOracle Date(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) ConvertToOracle Date = 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 -----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
> 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.)
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*****@dnartr eb.noraa> wrote in message
news:%2******** ********@TK2MSF TNGP10.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.)
> 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 ConvertToOracle Date(dtmDate)
Dim i
Dim firstSeparator
Dim secondSeparator
For i = 1 To CInt(Len(dtmDat e))
If Not IsNumeric(mid(d tmDate,i,1)) Then
firstSeparator = i
Exit For
End If
Next
For i = firstSeparator + 1 To CInt(Len(dtmDat e))
If Not IsNumeric(mid(d tmDate,i,1)) Then
secondSeparator = i
Exit For
End If
Next
'build date
Dim arData(2)
arData(0) = Right("0" & Mid(dtmDate,1,f irstSeparator-1),2)
arData(1) = UCase(MonthName (Right("0" &
Mid(dtmDate,fir stSeparator+1,s econdSeparator-firstSeparator-1),2),True))
arData(2) = Right(dtmDate,L en(dtmDate)-secondSeparator )
ConvertToOracle Date = Join(arData," ")
Erase arData
Set i = Nothing
Dim firstSeparator = Nothing
Dim secondSeparator = Nothing
End Function
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
> 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.) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 wrong way. Here what I have but
wow I get a wrong date.
$cdate = date("r",$cdate);
Please help!! I appreciate any help you can give.
Rick
|
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 = '01-SEP-02'
I'm getting no results. The date_and_time field is formatted like this:
2002-SEP-02 00:01:04
|
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 accept on an insert, however,
when it does a comparision validation it is failing. I have
StartDate = comparing to an invisible textbox that contains todays date
EndDate = comparing to StartDate needing to be >=
SetupDate = comparing to StartDate...
|
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
(Container.DataItem,"hiredate","{0:d}") %>'>
</asp:Label>
|
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 label control
which will be displaying the date is as follows:
text='<%# DataBinder.Eval(Container,"DataItem.events.eventdate") %>'
I know that there is a way to use a third parameter to format the output,
but I am not sure what this should...
| |
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 conversion functions but without success. I could
do a long winded function, but i hoped there would be a simple
solution.
Any Ideas.
|
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 timestamps, we frequently
use the date() function to "convert" from the Oracle date datatype to
the DB2 date datatype.
We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a...
|
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 need to display the date only,
not the time. But I don't want to change the datatype in the database
because the time is used in other places.
So what I am doing is pulling it out of the database, then modifying it in
ASP/VBScript by using the...
|
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 do??
-- Hyrum
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |