date from different countries

[Darin]

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 dd/mm/yyyy format but return a true
date, but in the format mm/dd/yyyy, similar to CDate. I don't want a
string, I want a real date.

I wrote a routine that changed the order to get it in the format, but it
is a string. When I then try to change it to a date, the Cdate routine
either changes the format or errors because it is trying to change the
format.

Dim junk As Date
Dim sjunk As String
junk = #1/1/1901#
Try
If Not IsDBNull(in_object) Then
sjunk = DRStr(in_object)
If
System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern <>
"M/d/yyyy" Then
' not USA
Dim xpattern As String
xpattern =
System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern
If Mid(xpattern, 1, 1) = "d" Then
' day is first
Dim ddate As String
Dim xflds() As String
xflds = Split(DRStr(sjunk), "/")
ddate = xflds(1) & "/" & xflds(0) & "/" & xflds(2)
sjunk = ddate
End If
End If
junk = CDate(sjunk)
Else
junk = CDate(in_object)
End If
Catch oExcept As Exception
'ShowMessage(oExcept.Message)
End Try
Return junk

The statement junk=cdate(sjunk) is what is changing the date back into
the global settings on the comptuer.

Any help would be greatly appreciated. I really wish CDate would allow
me to have an argument as the globalization settings allowing me to
force what setting I want the date to be. Don't forget, the FORMAT
command returns a string, not a date.

Thanks in advance.

Darin

 

[Stephany Young]

Dates in different cultures can be a bit of a minefield.

As long as the year part of the date is entered with 4 digits then that part
is not generally an issue.

The problems arise when you need to analyse what the user has entered for
the day and month parts.

If the day part is 13 or greater then that is easily identifiable as the day
because months are only 1 to 12.

If the day part and the month part are the same then there is no problem
because 01/01/2006 is easily identifiable in either dd/MM/yyyy or MM/dd/yyyy
formats.

If the day part is 1 to 12 and it is not the same as the month part then you
have some issues. E.G. Is 01/02/2007 meant to represent the 1st of February
or the 2nd of January.

If the instruction to the user is to enter the date as dd/MM/yyyy (or
MM/dd/yyyy) regardless of culture then one can use:

Dim _d As DateTime = DateTime.ParseExact(value, "dd/MM/yyyy")
' or MM/dd/yyyy as required

If the instruction to the user is to enter the date as per the culture of
the machine then one can use:

Dim _d As DateTime = DateTime.ParseExact(value,
DateTimeFormatInfo.CurrentInfo.ShortDatePattern)

Unfortunately, any given machine can have it's ShortDatePattern set to a
different pattern than the default pattern for the culture of the machine.

One way of overcoming this issue is to pad the parts of the date string to
make sure the day and month parts are the expected length:

Dim _parts As String() = value.Split("/"c)
_parts(0) = Parts(0).PadLeft(2, "0")
_parts(1) = Parts(1).PadLeft(2, "0")
value = String.Join(".", _parts)

For value = "1/1/2007", value will be modified to "01/01/2007" which will
now work with:

Dim _d As DateTime = DateTime.ParseExact(value, "dd/MM/yyyy")

 

[Michel Posseth [MCP]]

Just use universal date ISO 8601 formats

YYYYMMDD YYYY-MM-DD

hhmmss hh:mm:ss


this should work on any system regardless of there local settings

regards

Michel

 

[Cor Ligthert [MVP]]

Darin,

There is no #1-1-1900# or whatever that is the representation from the
debugger.

DateTimes are forever ticks starting at a not real moment (different for
dotNet and databases), which forever calculate the date and times based on
the culture settings of the user.

I prefer very often the CDate because it does a lot automatic.

Cor

 

[Darin]

Thanks.

I entered in today's date (18/1/2007) and then used:

DateTime.ParseExact(DRStr(in_object), "MM/dd/yyyy",
System.Globalization.DateTimeFormatInfo.CurrentInfo)

and it received an error that the string isn't a valid date (my computer
is set for Spanish(Mexico) so the date is dd/mm/yyyy)

Any ideas.

Darin

 

[Stephany Young]

Ummmmmm.... yeah!

18/1/2007 is clearly supposed to represent the 18th of January because 18 is
greater than the maximum number for month (12).

DateTime.ParseExact(DRStr(in_object), "MM/dd/yyyy",
System.Globalization.DateTimeFormatInfo.CurrentInfo)

specifies that the result of DRStr(in_object) MUST be in MM/dd/yyyy format.
If it is not in that EXACT format then it will throw an exception (as you
have observed).

18/1/2007 is actually dd/M/yyyy format.

Note that everything I said in my previous post was in context of your
statement that you wouldb entering dates in dd/MM/yyyy format (3rd
paragraph, 1st sentence).

For that you need:

DateTime.ParseExact(DRStr(in_object), "dd/MM/yyyy",
System.Globalization.DateTimeFormatInfo.CurrentInfo)

Note that each placeholder in the format string means a digit, so dd means
01 and not 1 and MM means 01 and not 1. Thus 18/01/2007 matches dd/MM/yyyy
format whereas 18/1/2007 matches dd/m/yy format.

 

[Darin]

But CDate won't work because it puts it in the format the computer is
set to and not the format I want it in.

Darin

 

[Darin]

I guess I don't understand what the parseexact is to do, and the
documentation wasn't too much of a help. When I did the following with
18/1/2007, I got the last message to be 18/1/2007, which makes it look
like the datetime is putting it back to the global settings.

sjunk = DRStr(in_object)
If System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern
<> "M/d/yyyy" Then
' not USA, create a string in USA format
Dim xpattern As String
xpattern =
System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern
If Mid(xpattern, 1, 1) = "d" Then 'd/m/y
' day is first
Dim ddate As String
Dim xflds() As String
xflds = Split(DRStr(sjunk), "/")
ddate = xflds(1) & "/" & xflds(0) & "/" & Mid(xflds(2), 1, 4)
'changed to m/d/y
sjunk = ddate
ShowMessage("A-" & sjunk) ' 1/18/2007
End If
End If
Dim format As New System.Globalization.CultureInfo("en-US", True)

ShowMessage("B-" & sjunk) ' 1/18/2007
ShowMessage("C-" & DRStr(DateTime.ParseExact(sjunk, "M/d/yyyy",
format))) ' 18/1/2007
Return DateTime.ParseExact(sjunk, "M/d/yyyy", format)

Darin

 

[Cor Ligthert [MVP]]

Darin,

I get more and more the idea that you want your Dates been showed in a way
that only people from the USA understand.

However there is no problem at all to do that.

Public Class Main
Public Shared Sub Main()
Threading.Thread.CurrentThread.CurrentCulture = _
New Globalization.CultureInfo("en-US")

MessageBox.Show(CDate("01-06-2004 11:59 PM").AddDays(1).ToString)

'And set the culture back when it was not your own culture
Threading.Thread.CurrentThread.CurrentCulture = _
Globalization.CultureInfo.InstalledUICulture
End Sub
End Class

Cor

 

[Darin]

Ok. I have a routine that accepts a date argument and needs a date
returned, but in true USA format.

public function USADate(in_date as date) As date
dim xout as date
Threading.Thread.CurrentThread.CurrentCulture = _
New Globalization.CultureInfo("en-US")

xout=cdate(in_date)

'And set the culture back when it was not your own culture
Threading.Thread.CurrentThread.CurrentCulture = _
Globalization.CultureInfo.InstalledUICulture

return xout
end function

If I change the culture in the routine, and change it back, then return
a date, what date is it going to be, USA format or Mexico? I just need a
routine that can take any date format and return a date in true US
format so it can write it to the database correctly.

Darin

 

[Stephany Young]

Take a step back for a moment.

Create a new Windows Forms project, place a button (Button1) on the form and
paste the following code into the
form.

Click the button and observe the results.

Change the initial date string and the 'foreign' culture to different values
and observe the results.

Once you have got a feel for what it is doing, then you can incorporate it
into your application.

Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Handles Button1.Click

Try
Console.WriteLine(GetUSDateString("19/1/2007"))
Catch _ex As Exception
Console.WriteLine(_ex.ToString)
End Try

End Sub

Private Function GetUSDateString(ByVal value As String) As String

' The following is commented out so we can simulate different cultures
'Dim myCI As CultureInfo = New
CultureInfo(CultureInfo.CurrentCulture.Name, False)

' The following line is used to simulate different cultures
' Change the en-NZ to es-MX or the name for any other culture you desire
Dim _myCI As CultureInfo = New CultureInfo("en-NZ", False)

' CultureInfo object for en-US culture
Dim _usCI As CultureInfo = New CultureInfo("en-US", False)

Dim _date As DateTime

Try
' Attempt to parse value using the 'foreign' culture
_date = DateTime.Parse(value, _myCI.DateTimeFormat)
Catch _ex As FormatException
' That didn't work
Try
' so Attempt to parse value using the en_US culture
_date = DateTime.Parse(value, _usCI.DateTimeFormat)
Catch _exx As FormatException
' That didn't work either
Try

' so Attempt to parse value using the en_US culture again after
swapping the first 2 parts of value about
_date = DateTime.Parse(Swap(value,
Convert.ToChar(_usCI.DateTimeFormat.DateSeparator)), _usCI.DateTimeFormat)
Catch _exxx As Exception
' That still didn;t work so throw the exception up the line
Throw
End Try
Catch _exx As Exception
' Some other exception happened so throw ot up the line
Throw
End Try
Catch _ex As Exception

' Some other exception happened so throw ot up the line
Throw
End Try

' One of the parse attempts succeeded so return the result in the
ShortDatePattern of the en_US culture
Return _date.ToString("d", _usCI.DateTimeFormat)

End Function

Private Function Swap(ByVal value As String, ByVal delimiter As Char) As
String

Dim _ss As String() = value.Split(delimiter)

Dim _s As String = _ss(0)

_ss(0) = _ss(1)

_ss(1) = _s

Return String.Join(delimiter, _ss)

End Function

 

[Cor Ligthert [MVP]]

Darin,

Your database is not set in USA format, it is in ticks from 1000/3
milliseconds starting at januari 1 1873 (for SQL server). It is in every
culture the same.

Cor

 

[Martin H.]

Hello Darin,

Ok. I have a routine that accepts a date argument and needs a date
returned, but in true USA format.

I understand that you want to get a date as a string like in traditional
BASIC?

Private Function BASICdate(byval ThisDate As Date) As String
Return (ThisDate.Month & "/" & ThisDate.Day & "/" & ThisDate.Year
End Function

Best regards,

Martin

 

[Armin Zingler]

Hello Darin,


I understand that you want to get a date as a string like in
traditional BASIC?

Private Function BASICdate(byval ThisDate As Date) As String
Return (ThisDate.Month & "/" & ThisDate.Day & "/" & ThisDate.Year
End Function

Why not use ThisDate.ToString instead of writing another function? The
format can be a constant.


Armin

 

[HKSHK]

Sorry, routine was not complete, now it is:

Private Function BASICdate(byval ThisDate As Date) As String
Return Format(ThisDate.Month,"00") & "/" & _
Format(ThisDate.Day,"00") & "/" & _
Cstr(ThisDate.Year)
End Function

Best regards,

Martin

 

[Jay B. Harlow [MVP - Outlook]]

Martin,
As Armin suggests: You can simplify that to:

Private Function BASICdate(byval ThisDate As Date) As String
Return ThisDate.ToString("MM/dd/yyyy")
End Function

Which also ensures that the year will be 4 digits.

 

[Branco Medeiros]

Ok. I have a routine that accepts a date argument and needs a date
returned, but in true USA format.

public function USADate(in_date as date) As date
dim xout as date
Threading.Thread.CurrentThread.CurrentCulture = _
New Globalization.CultureInfo("en-US")

xout=cdate(in_date)

'And set the culture back when it was not your own culture
Threading.Thread.CurrentThread.CurrentCulture = _
Globalization.CultureInfo.InstalledUICulture

return xout
end function

If I change the culture in the routine, and change it back, then return
a date, what date is it going to be, USA format or Mexico?

<snip>

Neither. A variable of type Date or DateTime is in a "neutral", binary
format, that has nothing to do with the thread's culture (and I guess
that's what Cor is trying to inform you).

It seems you're mistaking a date value with the date representation.
The date value is binary and, again, has nothing to do with a given
culture. The date representation, on the other side, is a string, and
varies according to the culture being used by the thread, the system's
date format, etc.

HTH.

Regards,

Branco.

 

[Martin H.]

Hello Jay,

As Armin suggests: You can simplify that to:

No, the simplification won't work, because the OP wrote that he wants to
have the US date. The US date has slashes in it (e.g. 01/31/2007).

However, the format command will replace the slashes with the separators
for your country. In my case (Germany) we use dots. With the simplified
routine I get 01.31.2007. To get the real BASIC date the best option is
to assemble the string as I did before.

Best regards,

Martin

 

[Martin H.]

Hello Branco,

Neither. A variable of type Date or DateTime is in a "neutral", binary
format, that has nothing to do with the thread's culture (and I guess
that's what Cor is trying to inform you).

It seems you're mistaking a date value with the date representation.
The date value is binary and, again, has nothing to do with a given
culture. The date representation, on the other side, is a string, and
varies according to the culture being used by the thread, the system's
date format, etc.

Correct and incorrect. You are correct that a date variable contains a
"neutral" binary format. However, you don't know what the OP wants to
accomplish. Perhaps he wants to use the date in an SQL query. In this
case he would have to use a string in US format.

Best regards,

Martin

 

[Branco Medeiros]

Martin H. wrote:

Correct and incorrect. You are correct that a date variable contains a
"neutral" binary format. However, you don't know what the OP wants to
accomplish. Perhaps he wants to use the date in an SQL query. In this
case he would have to use a string in US format.

<snip>

Notice that I didn't mention anything related to formatting the date, I
suppose others had already given that information. Therefore I don't
see the "incorrect" part of my post.

Regards,

Branco.