We help IT Professionals succeed at work.

Please help with International date formatting in VB.NET

DavidRothan
DavidRothan asked
on
I've written a licensing system in VB.NET for use with an application intended for a worldwide user base. I need the application to send a UK formatted 'registered date' string to my SQL license server, regardless of the country where the software has been installed. The SQL field that holds the date is NOT currently a datetime type. It's an nvarchar. Inside the application, when started, I need to do a date compare between the UK formatted 'registered date' on the server and the current UK formatted date on the client. Is there a way that I can force a UK format upon any date string I'm using in the application? Or, is there a simply a fundamentally better way of tackling this problem. For example...Is there any merit in setting the SQL field to a 'datetime' (which I understand is US format in SQL server) and just using the US format globally in the app? If I do this, will I then have to parse all UK formatted dates to US, before I attempt to write the date to the server?
Comment
Watch Question

Author

Commented:
Thanks. I've googled 'Date Parse' as well and found similar sites.
I'm still no closer to solving my problem though...
Todd GerbertSenior Engineer
Top Expert 2010

Commented:
1) I believe a datetime SQL type is stored as the number of seconds since January 1, 1900 - and thus is not format dependant.  You could make direct comparisons between a DateTime on a client and a datetime on the SQL server, assuming both are in the same time zone (you should always store your datetime's as UTC, and convert clients' DateTime's to UTC before making a comparison).
2) You can provide a CultureInfo object to DateTime.Parse:

Imports System.Globalization
Module Module1

    Sub Main()
        Dim ukDatetime As DateTime
        Dim ukDateString As String = "10.06.2010"
        Dim ukCulture As CultureInfo = CultureInfo.GetCultureInfo("uk")

        ukDatetime = DateTime.Parse(ukDateString, ukCulture)
    End Sub

End Module

Open in new window

Todd GerbertSenior Engineer
Top Expert 2010

Commented:
...of course that presumes the UK culture is installed on the client. Otherwise, use .ParseExact:
Imports System.Globalization
Module Module1

    Sub Main()
        Dim ukDatetime As DateTime
        Dim ukDateFormat As String = "dd.MM.yyyy"
        Dim ukDateString As String = "10.06.2010"

        ukDatetime = DateTime.ParseExact(ukDateString, ukDateFormat, Nothing)
    End Sub

End Module

Open in new window

Top Expert 2012
Commented:
>>Or, is there a simply a fundamentally better way of tackling this problem<<
Yes, use the appropriate data type, in this case datetime.

>>Is there any merit in setting the SQL field to a 'datetime' <<
Yes. You can actually compare it without tying yourself in knots in the process.

>>which I understand is US format in SQL server<<
Nope. Dates are not stored in MS SQL Server with any regional format. Period.

>>If I do this, will I then have to parse all UK formatted dates to US<<
Again no. You save dates using an unambiguous date format such as yyyymmdd
Top Expert 2012
Commented:
>>I believe a datetime SQL type is stored as the number of seconds since January 1, 1900<<
Actually not quite. It is all covered in the Remarks section of BOL, but here it goes:

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

Senior Engineer
Top Expert 2010
Commented:
I thought it was something like that, I remembered reading it earlier, but didn't see the details on MSDN (http://msdn.microsoft.com/en-us/library/ms187752.aspx) and didn't care to go hunting - at least I made the point that it's a number. ;)
 
Anywho...this nonsensical example kinda shows how you can retrieve a datetime from a SQL server and use the stored date to make a comparison with the current date.  The call to .ToUniversalTime may be moot if you're only comparing the date, and not time of day, but put it in there anyway for the sake of example.

Imports System.Data
Imports System.Data.SqlClient
Module Module1

    Sub Main()
        If GetRegisteredDate() > DateTime.Today.ToUniversalTime() Then
            MsgBox("Invalid registered date...")
        End If
    End Sub

    Function GetRegisteredDate() As DateTime
        Dim returnValue As DateTime
        Using cn As New SqlConnection("Your Connection String")
            cn.Open()
            Using cmd As SqlCommand = cn.CreateCommand()
                cmd.CommandText = "SELECT TOP 1 RegisteredDate FROM blah blah blah WHERE blah blah blah"
                returnValue = DirectCast(cmd.ExecuteScalar(), DateTime)
            End Using
        End Using
        Return returnValue
    End Function
End Module

Open in new window

Author

Commented:
I was beginning to suspect that I would have to do something like this. Thanks for clarifying.
So, 'smalldatetime' will do the job for my puposes. The next question I have then is, does System.Date in .NET work happily with setting and comparing dates stored in SQL as smalldatetimes?
Would I write:

System.DateTime.Today.Year.ToString & System.DateTime.Today.Month.ToString & System.DateTime.Today.Day.ToString to a smalldatetime in SQL server  when registering...
...and then:
Retrieve the value as a date when validating and DateDiff it with 'Now'.
Would that work? (before I start changing field types in the 'live' license server database) or will I have to parse 'Now' to match the format of the smalldatetime? Will a .NET 'Date', once it s value is set from a smalldatetime, return to it's culturally specific format?
Todd GerbertSenior Engineer
Top Expert 2010
Commented:
No, no strings needed. You need to get that out of your head - dates are numbers. ;)
You add a Parameter to your SqlCommand object, and assign a DateTime object to that Parameter.
 

Sub RegisterApplication(ByVal ClientId As String)
        Using cnx As New System.Data.SqlClient.SqlConnection("Your Connection String")
            cnx.Open()
            Using cmd As System.Data.SqlClient.SqlCommand = cnx.CreateCommand()
                cmd.CommandText = "INSERT INTO RegisteredTable (ClientId,RegisteredDate) VALUES(@ClientId,@RegisteredDate)"
                cmd.Parameters.AddWithValue("@ClientId", ClientId).DbType = DbType.String
                cmd.Parameters.AddWithValue("@RegisteredDate", DateTime.Today.ToUniversalTime()).DbType = DbType.Date
                cmd.ExecuteNonQuery()
            End Using
            cnx.Close()
        End Using
    End Sub

Open in new window

Todd GerbertSenior Engineer
Top Expert 2010

Commented:
See http:#32961202 for an example of retrieving a DateTime and comparing it to another DateTime.

Author

Commented:
Brilliant response experts! Thank you. Points split between the most useful contributions. I hope you think this is fair! I was tempted to bias the points in favour of tgerbet, for also introducing me to 'Using', which will now supersede the vast number of 'Dims' and 'Withs' littering my applications. How have I used .NET for so long and never understood the relevance and usefulness of 'Using' ?????

Author

Commented:
Ahhh, slight misinterpretation of 'Using'...
tgerbert...care to enlighten me on benefits of 'Using' over 'Dim'? From what I can make out, objects declared with 'Using' dispose themselves 'automatically' afer 'End Using'. Is that correct?
Todd GerbertSenior Engineer
Top Expert 2010

Commented:
Yes - but slightly more than that, it gurantees that the objects .Dispose() method will be called, even if an exception is thrown, and limits the scope of the variable to prevent me from inadvertently later attempting to use an object that has already been disposed.
Microsoft documentation suggests every object that has a .Dispose method (i.e. implements IDisposable) should be declared in a Using block - it's good here because your database server has limits, and you want to make sure connections are released as soon as possible after they're no longer needed - whether it's no longer needed because you're done with it, or no longer needed because an exception was thrown.
http://msdn.microsoft.com/en-us/library/htd05whh.aspx 

Author

Commented:
Fantastic explanation. Thank you.
I promise this is the last comment on the subject and then I'll leave you alone!
For my event log, I need date AND time. I've been playing around in a test table, using your solution posted above and with a field formatted as a datetime, as opposed to smalldatetime.
Using your solution, the time is always 23:00 (???)
Is it valid to use this...
.Parameters.AddWithValue("@TestDate", DateTime.Now.ToUniversalTime()).DbType = DbType.DateTime
or...
.Parameters.AddWithValue("@TestDate", DateTime.UtcNow).DbType = DbType.DateTime
...without running into cultural formatting issues?
Interestingly, when viewing the table in SQL Server, the date was displayed in a US format when I used your code, but when I use either of the above (into a datetime field), it displays as UK format! Not sure why this would be....
Todd GerbertSenior Engineer
Top Expert 2010

Commented:
Yes, the two examples you've shown are equivelant.
There really isn't any formatting going on at all since you're just storing/retrieving numbers - three apples is three apples no matter where in the world you are.

Author

Commented:
Do you know, I think I might just have found a case where three apples is three in two out of three cases...
The description is the end of the parameter string I used in the three tests.
Do you have any explanation for this? I can't even begin to think why that would happen.
 

capture.JPG
Todd GerbertSenior Engineer
Top Expert 2010

Commented:
DateTime.Today omits time information - actually it's today's date at midnight, June 10 12:00:00AM.  
So if it's locally June 10 00:00:00, and you're one time zone east of Greenwhich, then it'll get stored as a UTC = June 9 23:00:00 - but that's still June 10 your time.
Notice in http:#32961202 I made a comparison between GetRegisteredDate()'s return value and DateTime.Today.ToUniversal() becase I know GetRegisteredDate() is going to retrieve a UTC date/time from the database.
If you want to show the local time to the user, after getting the UTC from the database, you can use .ToLocal

Author

Commented:
My head hurts. I thought it might be time zone related, but then dismissed that as being far too clever!
I really want to thank you for your help this afternoon. All the very best to you!