How to identify the correct globalization for date types ?

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.
dimensionavAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dimensionavAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Reza RadConsultant, TrainerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dimensionavAuthor Commented:
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
Reza RadConsultant, TrainerCommented:
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
Reza RadConsultant, TrainerCommented:
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
HainKurtSr. System AnalystCommented:
0
dimensionavAuthor Commented:
reza_rad:
I am using an Access database.
0
HainKurtSr. System AnalystCommented:
or use format functions

FormatDateTime(Date[,NamedFormat])

or use DatePart
http://msdn.microsoft.com/en-us/library/4kt42529(VS.85).aspx
0
dimensionavAuthor Commented:
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
HainKurtSr. System AnalystCommented:
try it on access query until you are sure about that ;)
0
dimensionavAuthor Commented:
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
hassonookCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
dimensionav,
   Any comments about my post above..
0
dimensionavAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
oops.. Missed noticing it..
In Access that wont work..
0
CodeCruiserCommented:
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
pcelbaCommented:
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
ioanePlanning & Analytics ManagerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
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
harfangCommented:
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
ioanePlanning & Analytics ManagerCommented:
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
harfangCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.