VB.Net: Date reaches Access database in wrong format.

baabaa_nl
baabaa_nl used Ask the Experts™
on
Hi Experts,

I am developing an application on VB.Net and works with MS Access database.
The application I am developing will be used by users that have European Culture in their computers and the dates they use are dd/mm/yyyy.

But when I send the date as 17/05/2012 (the month is May) via VB.Net to Access database , in the Access database it gets stored as 07/17/2012(the month is stored as July, and it is changing the date to US date format ). So to solve this I had to send the date with the name of the month, like 17/May/2012.

I got the name of the month by using the dateObject.ToString("MMMM").
But when I deployed this application on my client computer who was using Netherland culture, got errors and I found the error as that the month May was getting passed as 'Mei in Dutch'. And it was because the Access was not Dutch but English. Since Access that they were using was an English version it could not identify the months in Dutch.

So is there any solution that I can solve this problem by making sure that the date I send in European format is stored as an European date format in Access, because after it reaches into Access it is being converted to US date format.

How can I solve this problem.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<So is there any solution that I can solve this problem by making sure that the date I send in European format is stored as an European date format in Access, because after it reaches into Access it is being converted to US date format.>>

  Date/times in a JET DB are stored as a double float and the default format is assumed to be US.  You must also use US data formatting in any query against the DB.  There is no way to change this.

Jim.
change the datatype of your date column to varchar and store the date as string.
Again when querying convert the string to date, this looks to be the only option here...
Commented:
It sounds like your users are inputting dates as strings.  Use a datepicker instead which gives you a DateTime value.  

Sending the date to the database, you can avoid using strings by using parameters.  This is my preferred method.

FAQ: How do I make a parameterized query in the database with VB.NET?
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/6bdf8b71-1cf1-41c0-848c-4fca2c9e1ea2/

If you really need to send the date as a string to the database, the Access date literals with hash marks (#4/18/2012#) are formatted US-en style.  You can also use DateSerial function

MsgBox(String.Format("DateSerial({0:yyyy, M, d})", Today))
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thank you JDettman for your quick response.

Hi BuggyCoder,

But I will be running the query from the access it self and than retrieve the data, so when I am trying to query data between two dates wouldn't that be a problem to query? Because when I try to retrieve from two different dates, the date would be in string already in the database, and not in date format.
exactly, that's why i told you that you need to convert this string to date in access and then fire your query:-

Format (#17/04/2004#, "Short Date")       would return '17/04/2004'
Format (#17/04/2004#, "Long Date")       would return 'April 17, 2004'
Format (#17/04/2004#, "yyyy/mm/dd")       would return '2004/04/17'
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
<<So is there any solution that I can solve this problem by making sure that the date I send in European format is stored as an European date format in Access, because after it reaches into Access it is being converted to US date format.>>

Yes. Don't. This is the wrong method. Dates inputted as string expressions in SQL statements must be formatted either in the "reverse" US-format (m/d/yyyy) or - preferred and the default for most modern database engines like SQL Server - the ISO format (yyyy/mm/dd).

Also, have in mind that the displayed format of a date has nothing to do with how the date value is stored in the table.

<<change the datatype of your date column to varchar and store the date as string.>>

No. Never ever do this. Date and times should always - no exceptions - be stored with data type DateTime. Convert string expressions of dates before saving and, when reading, format to fit your purpose if string expressions are needed.

/gustav
Jdetterman has identified the correct key to the problem.  Regardless of what format you entered the date into the database (assuming it was a valid format) it is stored as a binary datetime value.  How it is displayed or retieved from the database depends entirely on the query, country code and date format settings.

The solution to your problem is to query the result in the format you want like:
      SELECT Format([your_date],"DD/MM/YY") AS [TheDate]
      FROM your_table;
Assuming your date column is "your_date", the results of the "TheDate" column in this query will be formatted as you prefer.

Storing dates as string data is a BAD idea if for no other reason then it will allow you to store Feb 31, -999  which is clearly not a valid date.

Sean

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial