Link to home
Start Free TrialLog in
Avatar of DavidRothan
DavidRothanFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Please help with International date formatting in VB.NET

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?
Avatar of designweboxx
designweboxx
Flag of Switzerland image

Avatar of DavidRothan

ASKER

Thanks. I've googled 'Date Parse' as well and found similar sites.
I'm still no closer to solving my problem though...
Avatar of Todd Gerbert
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

...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

SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
See http:#32961202 for an example of retrieving a DateTime and comparing it to another DateTime.
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' ?????
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?
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 
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....
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.
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
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
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!