How to convert other language date to US format date in vb in access db

Question

1.00/5 (1 vote)

See more:

Hi we develop web application,currently need to support other language date type also.
When we save date in Date/Time column in access db we convert to US format and save it,but retrieve date value from access db,it show in other language(2558/08/14 Thai format,bcz web application running in Thailand OS) but copy/paste that access db in English version OS and open access db,the date in (14/8/2015 US format)
So how to convert Thai date to US format from datatable.

Below is i tried

VB

Dim iString As String = row(0).ToString 'here row(0) have 2558/08/14
Dim oDate As DateTime = DateTime.ParseExact(iString, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture)

It show "String was not recognized as a valid DateTime." error

Note:Web application running in Thai OS,when save date in access db we convert to US format using following code

VB

DateTime.Now.ToString("dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture)

Pls check screenshot taken form Thai OS,which install visual studio in Thai OS
http://prntscr.com/85jofq[^]
http://prntscr.com/85jodq[^]

I am create new web application and windows application inside Thai OS using VS2010
This is web application asp button click event

VB

Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
    Dim cn As OleDbConnection
    Dim cmd As OleDbDataAdapter
    Dim dt As New DataTable
    Dim strcmd As String = ""
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files (x86)\OCR\UDatabase\MM.mdb; Jet OLEDB:Database Password= Password;")
    strcmd = "SELECT Format(ProcessDate,"yyyy/MM/dd") as PDate FROM tblFile "
    cmd = New OleDbDataAdapter(New OleDbCommand(strcmd))
    cmd.SelectCommand.Connection = cn
    cn.Open()
    cmd.Fill(dt)
    Label1.Text = dt.Rows(0)("ProcessDate")
    cn.Close()
End Sub

My web config is

XML

<?xml version="1.0"?>
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>
</configuration>

In windows application button click event is

VB

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
   Dim cn As OleDbConnection
        Dim cmd As OleDbDataAdapter
        Dim dt As New DataTable
        Dim strcmd As String = ""
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files (x86)\OCR\UDatabase\MM.mdb; Jet OLEDB:Database Password= Password;")
        strcmd = "SELECT Format(ProcessDate,"yyyy/MM/dd") as PDate FROM tblFile "
        cmd = New OleDbDataAdapter(New OleDbCommand(strcmd))
        cmd.SelectCommand.Connection = cn
        cn.Open()
        cmd.Fill(dt)
        Label1.Text = dt.Rows(0)("ProcessDate")
        cn.Close()

End Sub

in Access db got this value in tblFile table

SQL

ID	 ProcessDate	 
200	 14/8/2015 4:41:51 PM

In windows application same US format date showing,but in web application it show Thai date format.

Note: i am running in Thai OS with VS2010 ,need to specify and date culture in web config ?

Pls check this link for exe and web application result
https://www.dropbox.com/s/3b3ano2xnqph0ls/thaios.wmv?dl=0[^]

Posted 16-Aug-15 19:44pm

Aravindba

Updated 19-Aug-15 15:00pm

v10

Add a Solution

Comments

Patrice T 17-Aug-15 2:06am

Are you saying that you have a string "2558/08/14" and you want to parse it as a DateTime using "MM/dd/yyyy" and you wonder why it fails ?
Serious ?

Aravindba 17-Aug-15 7:23am

"2558/08/14" this is not string,this is date/time format in Thai OS.when retrieving form access db i get like this "2558/08/14",so after getting this,how to convert to US Format ?

Patrice T 17-Aug-15 14:28pm

On line 1, iString look furiously like a string equal to "2558/08/14" (from the comment).
On line 2, you try to parse iString (which is "yyyy/mm/dd") with date format which expect "mm/dd/yyyy"

Aravindba 17-Aug-15 20:07pm

If u search Thailand date in google,u can see date like "255/08/14" -> yyyy/MM/dd
and i am try to get in the format of "yyyy/MM/dd" but in my access db date have 14/8/2015 4:41:51 PM datatype is Date/Time and i am not set any Format in that filed in access db,i think by default it consider as General Date.
In US format year is 2015 and Thai format year 2015 is year 2558.

Thailand mainly uses the Buddhist Era which is 543 years ahead of the Gregorian year. The year AD 2015 is indicated as 2558 BE in Thailand. Despite adopting ISO 8601, Thai official date is still written in DDMMYYYY format, such as 16 July 2558 BE (AD 2015) or 16/07/2558.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2015-08-16T20:06:00

Solution 1

You better install UTC time in the database, use DateTime.ToUniversalTime Method[^] to convert local datetime to UTC. when client application need to show datetime, you can convert UTC time back to local datetime using DateTime.ToLocalTime Method[^]

Posted 16-Aug-15 20:06pm

DamithSL

Wendelius · Answer 2 · 2015-08-16T20:07:00

Solution 2

If I understand the situation correctly the underlying type in the database is correctly Date/Time but in inserts and updates you provide the value as a string literal.

If this is the case, you should use parameters since they help you to overcome such conversions problems. With parameters you can set the parameter value with the regional settings that apply at client side and they are converted properly when added to the database.

Depending what technology you use the parameter classes may be different, but if you operate with OLE DB, have a look at OleDbParameter[^]

Posted 16-Aug-15 20:07pm

Wendelius

Comments

Aravindba 17-Aug-15 2:20am

Hi when save date in date column no problem,it save in US format,but when select date value form access db and fill in datatable,in that datatable we get thai date,bcz application running in Thai OS

Aravindba 17-Aug-15 4:11am

pls check this links,we install VS2010 in Thai os and trace the code
http://prntscr.com/85jofq
http://prntscr.com/85jodq

Wendelius 18-Aug-15 0:30am

Not sure if I understand this correctly but in the example in http://prntscr.com/85jodq you're trying to convert cfdate to datetime which already is a date. I see no reason to do that.

Aravindba 18-Aug-15 1:46am

If i change cfdate as string and get value from row(0) and try to convert string to date in dd.still getting thai format date ,if Dim cfdate as string and Dim dd as Date

Wendelius 18-Aug-15 15:36pm

Sorry if I'm completely off-track, but consider the following code

string date1 = System.DateTime.Now.ToString(new System.Globalization.CultureInfo("th-TH"));
System.Diagnostics.Debug.WriteLine(date1); // 18/8/2558 22:32:11, correct

System.DateTime date2;

System.DateTime.TryParse(date1, out date2);
System.Diagnostics.Debug.WriteLine(date2); // 18.8.2558 22:32:11, incorrect, this should be my local date format so the year is wrong

System.DateTime.TryParse(date1, new System.Globalization.CultureInfo("th-TH"), System.Globalization.DateTimeStyles.None, out date2);
System.Diagnostics.Debug.WriteLine(date2); //18.8.2015 22:32:11, correct, Thai format is convered to European format

As you see, if I provide correct cultureinfo the conversion is done successfully. One important thing to notice is that the date does not natively have any format since dates are internally handled as numbers, not strings.

Aravindba 18-Aug-15 22:21pm

Thank you for u reply,recently i update my description with web and windows application button click event code,in that i wrote code without use culture i retrieve date value form access db,In that access db i save date like "14/8/2015 4:41:51 PM",in windows application correctly show like "14/8/2015 4:41:51 PM" ,but in web application date is convert to Thai date like "255/08/14".
OK now come with ur code,below code poated by u,i just show date in 3 textboxes

Dim date1 As String = System.DateTime.Now.ToString(New System.Globalization.CultureInfo("th-TH"))
TextBox1.Text = date1

Dim date2 As System.DateTime
System.DateTime.TryParse(date1, date2)
TextBox2.Text = date2.ToString()

System.DateTime.TryParse(date1, New System.Globalization.CultureInfo("th-TH"), System.Globalization.DateTimeStyles.None, date2)
TextBox3.Text = date2.ToString("dd/MM/yyyy")

In windows form application no problem,but same code in web application i get result like this pls see screenshot http://prntscr.com/869vmv

Note: i am running this code inside Thai language OS,Not in English language OS,bcz in English lang OS can convert to Thai date and again covert to US format.
This is OS information http://prntscr.com/86a8wk

Wendelius 19-Aug-15 0:03am

As far as I can see that looks good since you're running it in Thai version.

So now if the data is stored inside an Access database it is stored as a number. What comes to showing the date in other formats, you should somewhere store the culture of the user and based on that, format the date. Consider the following example

System.DateTime date3 = System.DateTime.Now;
System.Diagnostics.Debug.WriteLine(date3.ToString(new System.Globalization.CultureInfo("th-TH")));
System.Diagnostics.Debug.WriteLine(date3.ToString(new System.Globalization.CultureInfo("en-US")));
System.Diagnostics.Debug.WriteLine(date3.ToString(new System.Globalization.CultureInfo("fr-FR")));
System.Diagnostics.Debug.WriteLine(date3.ToString(new System.Globalization.CultureInfo("fi-FI")));

The thing is that you need to always store the date in the same way. When the user inputs a date, you would convert it to a date using again the users culture and then using a parameter you would store it into Access database. This way the dates in the database would always be correct since they all are saved using the same culture.

Would that make sense?

Aravindba 19-Aug-15 0:14am

what u expect from me ? need to save date in access db in the format of String data type or Number data type or Us format Date(19-08-2015) data type or System Culture Date format (19-08-2558) data type ?

Again and again i told u sir ,in windows and console application above code(urs code and also code form my question) work fine,in Web Application only problem,in windows and Console application no need to convert to US format(date3.ToString(new System.Globalization.CultureInfo("en-US"))) when retrieve date from access db,bcz i save date in access db that time we convert to US format and save in access db.
Even i post video also
https://www.dropbox.com/s/3b3ano2xnqph0ls/thaios.wmv?dl=0
Web and windows application running inside Thai version OS.

Wendelius 19-Aug-15 0:22am

The date needs to be stored in date format, not as string and not as a number. The Access stores the date internally as a number so that the program does not need to worry about the formats.

Let's go back to basics, have I understood the problem correctly? Please correct if there is a mistake in the following.
- Thai user enters date in Thai format, successfully saved to the database?
- Thai user fetches the date from the database, correctly shown in the web page
- US user enters date in US format, incorrectly saved to the database?
- US user fetches the date from the database, incorrectly shown in the web page?

Aravindba 19-Aug-15 21:01pm

Ok,Thanx,now work,if remove Format function(Format(ProcessDate,"yyyy/MM/dd")) from select query it works fine,

Wendelius 20-Aug-15 14:09pm

Glad to hear it's solved!

Maciej Los · Answer 3 · 2015-08-16T20:25:00

Solution 3

The best way to store date data type in MS Access database is to use ISO format[^].

For further information, please see: How to store, calculate, and compare Date/Time data in Microsoft Access [^]

Posted 16-Aug-15 20:25pm

Maciej Los