?
Solved

How to identify the correct globalization for date types ?

Posted on 2010-01-11
24
Medium Priority
?
682 Views
Last Modified: 2012-05-08
HI

I need to compare date values against an access database using asp.net (vb.net) but I am confused because the returned value from the following code "Dim hoy As Date = Date.Today.Date", seems to be dd/mm/yyyy but the database interprets it as mm/dd/yyyy.

How could standarize date values in order to make access interpret them always with the same format ?
Does this something to do with the language of access and server OS ?

I have noticed that if I use the wizard of MS Access and I put a date like dd/mm/yyyy the SQL edit mode shows it as mm/dd/yyyy.

regards.
0
Comment
Question by:dimensionav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +6
24 Comments
 

Author Comment

by:dimensionav
ID: 26290179
In my computer I have Spanish language and Mexico as Region. ( it supposed to be dd/mm/yyyy format). MS Access is in spanish too.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26290221
Just use CAST/ CONVERT function in SQL Server to get it converted to the required format like

WHERE datetime_col_sql = CONVERT(datetime, datetime_col_access, 103)

Kindly pass datetime_col_access in Character with format 'dd/mm/yyyy' and it should work..
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26290227
you can use your date with format when you want to use them as string like:
Response.Write(DateTime.Now.ToString("dd/MM/yyyy")
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:dimensionav
ID: 26290286
reza_rad:
I did what you posted but seems that the problem is that access is interpreting ( or expecting ) the string as mm/dd/yyyy.

In fact I did an access demo database and after creating a table and a query the date is still interpreted as mm/dd/yyyy, just like this:

SELECT [DEMO].[DATE_DATA]
FROM DEMO
WHERE ((([DEMO].[DATE_DATA])=#1/11/2010#));

I wonder if that string format is native in any language of access and I should "force" my date strings as access expects them.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26290309
if you have different date formats in sql server stored
I suggest to use DateDiff in your sql queries to check date fields,
like below:


SELECT [DEMO].[DATE_DATA]
FROM DEMO
WHERE Datediff(Day,[DEMO].[DATE_DATA],'1/11/2010')=0;

Open in new window

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26290331
this is more reliable :

SELECT [DEMO].[DATE_DATA]
FROM DEMO
WHERE Datediff(Day,[DEMO].[DATE_DATA],convert(datetime,'1/11/2010'))=0;

Open in new window

0
 
LVL 57

Expert Comment

by:HainKurt
ID: 26290388
0
 

Author Comment

by:dimensionav
ID: 26290399
reza_rad:
I am using an Access database.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 26290417
or use format functions

FormatDateTime(Date[,NamedFormat])

or use DatePart
http://msdn.microsoft.com/en-us/library/4kt42529(VS.85).aspx
0
 

Author Comment

by:dimensionav
ID: 26290594
I did this:
Dim hoy As String = Date.Today.ToString("MM/dd/yyyy")
and worked, but my concern is if Access will be always interpreting date formats as "MM/dd/yyyy" no matter what language or globalization settings are configured.

Regards.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 26290606
try it on access query until you are sure about that ;)
0
 

Author Comment

by:dimensionav
ID: 26290638
I have noticed that if I use the wizard of MS Access and I put a date like dd/mm/yyyy the SQL edit mode shows it as mm/dd/yyyy.
0
 
LVL 2

Expert Comment

by:hassonook
ID: 26290959
If this is a local Access application, it uses your system's date time format, so changing your localization settings in Windows to use dd/mm/yyyy will make Access convert that way, unless this has been overridden somewhere in the app.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26291372
dimensionav,
   Any comments about my post above..
0
 

Author Comment

by:dimensionav
ID: 26292995
rrjegan17:
I am using Access and seems that your functions are for MS SQL Server. Do you know something similar for Access ?

hassonook:
I think Access is the problem in some way because my regional configuration in windows and even Visual Studio are forced to "dd/MM/yyyy", setting the Culture and UI Culture for ASP.NET Web Page Globalization.



0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26293319
oops.. Missed noticing it..
In Access that wont work..
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 26302930
You may want to use the Format function along with either cDate or DateValue functions

http://www.techonthenet.com/access/functions/date/format.php
0
 
LVL 42

Expert Comment

by:pcelba
ID: 26307726
Access 2007 is reading date format from OS regional settings. To override it you have to use FORMAT function.

SQL Server Management Studio uses SQL settings and behaves differently in various versions... Date setting is determined for each user login. You may read the following blog to learn more: http://blogs.msdn.com/sqlserverfaq/archive/2009/11/13/how-to-change-date-format-after-installing-sql-server.aspx

The best date format for program code is YYYY.MM.DD. This is not obvious for all users around the world, so some input/output formatting is a standard. Whereas you may use CONVERT function on output, the input should be based on app configuration and users should always see the required date format.
0
 
LVL 13

Accepted Solution

by:
ioane earned 1000 total points
ID: 26308896
Hi dimensionav,

Access interprets date values according to the local regional settings of the computer it is being run on but not for the SQL interpreter which "I believe" always uses "MM/DD/YYYY" format.

Best bet, IMHO, is to use the DateValue function in your code.

The DateValue function will interpret the date according to the local regional settings and pass the equivalent numerical value to the database (which is format independent).

eg.
UPDATE Table1 SET Table1.ExampleDate TO DateValue("dd/mm/yyyy")

If you need date and time then:
UPDATE Table1 SET Table1.ExampleDate TO DateValue("dd/mm/yyyy") + TimeValue("hh:mm:ss")
0
 
LVL 58

Assisted Solution

by:harfang
harfang earned 1000 total points
ID: 26309410
Hello,

I'm quite familiar with the problem, as computers in Geneva use US and various European settings.

In short: use ISO dates everywhere: 2010-01-04

I even changed my regional settings to use ISO, and all my applications display and print only ISO dates.

Access will use your regional settings. If you have a d/m/y order, this will be used everywhere:
* in the query design grid
* for the default formats like "general date"
* when converting a date to a string implicitly (not with Format())
* when converting a string to a date

VBA works the same. Functions like CDate() and implicit date to string conversions will use the regional settings.

SQL knows only American. So the SQL view will display dates and times in US format. Always. However, if you create the SQL string, you can use ISO dates. They are always interpreted correctly.

Finally, when communicating with Access (or the Jet Engine), there are only two safe ways to pass dates: the serial date (the date as a number, using CLng() or CDbl() if needed) or ISO date strings.

Cheers!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 26309454
Tramtrak,

Your suggestion is a typical example of implicit conversion:

    DateValue("dd/mm/yyyy")

The string will be converted using the regional settings. If the result doesn't work, it will try to switch between d/m and m/d -- a horrible design decision. Instead, use of of:

    #2010-01-04#
    DateSerial(2010, 1, 4)

(°v°)
0
 
LVL 13

Expert Comment

by:ioane
ID: 26310115
Hi Harfang,

Thanks for clearing some things up there.

The reason I suggested using the Implicit value is purely so that the users date format settings are taken into account when interpretting the date. i.e. if a user enters the date as "mm/dd/yyyy", then the code will interpret that as per the user's settings.

Using DateSerial(yyyy-mm-dd) requires the programmer to know which date format the user's system is using to avoid the month/day issue.

The only other way to get around this would be to stipulate, to the user, the data entry format.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26310598
Absolutely. When handling user input, it's best to let the system make the conversion. After that, the date should either remain in a date variable or field (stored as a number) or transferred in ISO format when a string is required.

(°v°)
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question