473,881 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date Format and using database in various countries

Hello all,

General question for back-end database that has numerous date fields where
the database will be used in regions that put the month first, and regions
that do not.

Should I save a date format in the table design, such as: mm/dd/yyyy?

What I've done for years is to store the date format in date fields, then on
the forms, based on their region, I would set the date formats on form_load
depending on their preference of mm/dd/yyyy or dd/mm/yyyy. To try to combat
confusion, I added a 3rd preferred date format of: mmm-dd.yyyy, where the
month will be 3 letter designations.

Just curious what others are doing that deal with a database that can be
used globally.

Many Thanks,

Sep 14 '07
10 5835
ARC wrote:
I have many customers in Australia and the UK, but the bulk in the US. After
numerous enquiries from Australian users with bizzare behavior using
dd/mm/yyyy, I did end up adding a date format option for either dd-mmm-yyyy
or mmm-dd-yyyy, and that, as you say, eliminates all doubt regarding the
"SmartbizAustra lia" <to*@smartbiz.c om.auwrote in message
news:11******** **************@ 57g2000hsv.goog legroups.com...
>Dates are a serious issue in Access especially down under in

We use the british system and cannot figure out why access uses the mm
\dd\yyyy format sometimes.

The way we always do vba is to use MMM where applicable.

That way there is no doubt as to the date...

This works against sql server as well so it is more universal than the
function to switch at date to the american format above.
No offense meant to Allen but just use format to make your month three
characters and life is easier!

e.g. format(MyDate() ,"dd-mmm-yyyy") works with no issues against any
system....fo r australian/UK users and even using format(mydate() ,"mmm-
dd-yyyy") for us systems works fine..

Note: the format - yyyy-mm-dd is one of the best for queries against
other databases - e.g. dbII or sql server......and most systems
shouldn't have an issue with this.....

Tom Bizannes
Sydney, Australia
I would suggest that you carefully reread the replies of Allen Browne
and Rick Brandt, and also the article Allen Browne linked to.

When doing dynamic SQL, mmm-dd-yyyy or dd-mmmm-yyyy are *not* safe

For instance, they will barf with the settings I use. In particular for
December (des) and October (okt).

Both the format given by Allen Browne mm\/dd\/yyyy and the format given
by Rick Brandt yyyy-mm-dd are safe (the latter is also called ISO 8601).

If your "bizarre behaviour" relates to the interface, the easiest is
what is also already suggested, don't use any format at all for bound
controls. That way, the operators will get the format of their

Sep 16 '07 #11

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

Similar topics

by: Dwalker | last post by:
I just want to enter today's date in a text input box with the format mm/dd/yyyy when it receives focus. I've been playing around with this but just can't seem to get it to work. Any help would be appreciated. Doug
by: koray | last post by:
hi everyone, in my form i have to take some date information in dd-mm-yy format. but i don't want user to use tabs while typing. for example s/he should simply type 280104 but 28/01/04 must appear. what can i do for that? should i use three input tags? but then, how can i make the cursor jump to the next field when typing in current field is done? or if i use one input tag, how can i keep '/' signs in the field fixed
by: Rotsj | last post by:
Hi, i'm using visual foxpro 9 with a mysql 5 database, for direct access to my database i use navicat. In visual foxpro i've set my date format to dd-mm-yyyy, also i did this in navicat. However when i insert a date into my database i have to insert it with the format yyyy-mm-dd. Is this a setting in mysql or is there another reason? Thanks. Rotsj.
by: x | last post by:
hi i am a pilot by profession. i want to create a database of my logbook using ms access 2002. i am facing a problem regarding the format of time field. when i select "Data/Time" data type for my time field then this format gives the liberty to record times uptill a figure of 59 in different sub-formats, whereas i want the format to be able to record the times like 80:35 or 1:10 or 1138:00. which means that i have these many hours on a...
by: insomniux | last post by:
Hi, I am having a problem with formatting the default value of a date field. It has been discussed earlier in many topics, but still I cannot solve the problem. What's happening: I have various forms which are based on an ODBC-linked tables. In one of the forms, I have a control which shows the date of a date field in my database (storage type=date). The default value for the control is set to '=Date()', the format property is set to...
by: Petyr David | last post by:
Just looking for the simplest. right now my perl script returns an error messge to the user if the date string is invalid. would like to do this before accessing the server. TX
by: Darin | last post by:
I have an applicatoin that works 100% perfect when running on a machine setup for English (United States), but when I change it to Spanish (Mexico), the dates start giving me fits. THe reason is USA is mm/dd/yyyy and mexico is dd/mm/yyyy. So, with the computer set to mexico, any standard CDATE function is going to return the date in the dd/mm/yyyy setting since that is what the computer is set to. I want to be able to enter a date in...
by: fniles | last post by:
On my machine in the office I change the computer setting to English (UK) so the date format is dd/mm/yyyy instead of mm/dd/yyyy for US. This problem happens in either Access or SQL Server. In the database I have a table with Date/time column. The database is located on a machine that is set to dd/mm/yyyy also. When I enter date 7/1/08 (as in January 7, 2008), it stores it in the database as 1/7/08 instead of 7/1/08. Why is it like that...
by: Martin | last post by:
I'm retrieving some records from a database. One of the fields contains a date/time. I would like to format it as I send it out to the table in the displayed page. Can some one please tell me how I can/should do that? Or possibly point me to an on-line explanation? Thanks ps: if it makes any difference, the data is coming from a MS Access
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: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
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: 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: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.