473,698 Members | 2,615 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date Format and using database in various countries

ARC
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,

Andy
Sep 14 '07 #1
10 5810
Internally, Access stores date/time values as real numbers, where the
integer part represents the date (number days since Dec 30, 1899) and the
time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)

At display time, Access uses the date format for the computer displaying the
value (unless you specify a format in the property of the field in table
design, or the text box on the form.)

For details on how to avoid the 3 cases where Access interprets your entries
incorrectly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <an**@andyc.com wrote in message
news:y4******** *********@nlpi0 68.nbdc.sbc.com ...
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,

Andy
Sep 15 '07 #2
ARC
Thanks for the link, Allen. I do use the format field for unbound date entry
(for critieria fields on a reports screen, for example).

The only thing I can't tell from your link is the answer to the question on
whether or not to save a date format in the table design for date fields. My
original thinking in doing so was in 1999, to make sure that access was
actually saving the year's as 4 digits. So I used mm/dd/yyyy in the format
in table design.

Not sure if I should be doing this anymore.

Thanks!
"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:46******** *************** @per-qv1-newsreader-01.iinet.net.au ...
Internally, Access stores date/time values as real numbers, where the
integer part represents the date (number days since Dec 30, 1899) and the
time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)

At display time, Access uses the date format for the computer displaying
the value (unless you specify a format in the property of the field in
table design, or the text box on the form.)

For details on how to avoid the 3 cases where Access interprets your
entries incorrectly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <an**@andyc.com wrote in message
news:y4******** *********@nlpi0 68.nbdc.sbc.com ...
>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,

Andy

Sep 15 '07 #3
ARC wrote:
Thanks for the link, Allen. I do use the format field for unbound
date entry (for critieria fields on a reports screen, for example).

The only thing I can't tell from your link is the answer to the
question on whether or not to save a date format in the table design
for date fields. My original thinking in doing so was in 1999, to
make sure that access was actually saving the year's as 4 digits. So
I used mm/dd/yyyy in the format in table design.

Not sure if I should be doing this anymore.
Makes no difference to how it is stored (never did). What is stored is a
number.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 15 '07 #4
As Rick says, it is best not to use anything in the Format property of the
date/time field in your table, nor on the Format property of controls bound
to a date/time field.

That way your application will work in accordance with the user's settings
(in the Regional Settings of the Windows Control Panel.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <an**@andyc.com wrote in message
news:CQ******** *******@newssvr 25.news.prodigy .net...
Thanks for the link, Allen. I do use the format field for unbound date
entry (for critieria fields on a reports screen, for example).

The only thing I can't tell from your link is the answer to the question
on whether or not to save a date format in the table design for date
fields. My original thinking in doing so was in 1999, to make sure that
access was actually saving the year's as 4 digits. So I used mm/dd/yyyy in
the format in table design.

Not sure if I should be doing this anymore.

Thanks!
"Allen Browne" <Al*********@Se eSig.Invalidwro te in message
news:46******** *************** @per-qv1-newsreader-01.iinet.net.au ...
>Internally, Access stores date/time values as real numbers, where the
integer part represents the date (number days since Dec 30, 1899) and the
time is stored as a fraction of a day (0.5 = noon, 0.25 = 6am, etc.)

At display time, Access uses the date format for the computer displaying
the value (unless you specify a format in the property of the field in
table design, or the text box on the form.)

For details on how to avoid the 3 cases where Access interprets your
entries incorrectly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

"ARC" <an**@andyc.com wrote in message
news:y4******* **********@nlpi 068.nbdc.sbc.co m...
>>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.
Sep 15 '07 #5
ARC
I do use date input masks however, to make date entry easier. I let the user
pick the default date entry format. Any problems with this?

Thanks guys!
Sep 15 '07 #6
That's a question of style

Personally I don't use input masks. They do nothing to ensure the date
format is correct, and they actually slow users down. A good data entry
operator knows they can enter dates for the current year with just 3 -5
keystrokes, e.g.:
1/1

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ARC" <an**@andyc.com wrote in message
news:Kl******** *********@newss vr23.news.prodi gy.net...
>I do use date input masks however, to make date entry easier. I let the
user pick the default date entry format. Any problems with this?

Thanks guys!
Sep 15 '07 #7
Dates are a serious issue in Access especially down under in
Australia!

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....for 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.....

Regards,
Tom Bizannes
Sydney, Australia

Sep 16 '07 #8
SmartbizAustral ia wrote:
Dates are a serious issue in Access especially down under in
Australia!

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...
Except in a foreign language version where the month abbreviations are not the
same. Stick with yyyy-mm-dd. That one always works.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 16 '07 #9
ARC
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
date.
"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
Australia!

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....for 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.....

Regards,
Tom Bizannes
Sydney, Australia

Sep 16 '07 #10

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

Similar topics

9
23671
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
25
19846
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
1
5082
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.
2
2467
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...
9
6368
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...
17
5272
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
21
3375
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...
30
5701
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...
9
2930
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
0
8685
marktang
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...
0
8612
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,...
0
9171
Oralloy
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...
0
9032
jinu1996
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...
1
8905
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,...
0
7743
agi2029
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...
0
4373
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...
1
3053
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
2
2342
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.