How we can resolve the datetime problem shifting the Access DB from production server to live

Question

Hi,

Can you advice best way to rectify timezone issues in .Net. Recently I developed a simlple website by using asp.net C# as codebehind adn MS Access as backend.

My production server and live servers are in different date time setting. My producttion server date format is dd-mm-yyyy

live server format is mm-dd-yyyy.

I am facing an error when i tried to cast the datetime in front end. "String was not recognized as a valid DateTime."

The date i try to cast was populated in my production server it works fine in production server. but when i push the access file into live server I am facing the above error. any help would much appreciated

Answer 1

I apologize that I don't do c#, but in VB, I do the following (this is a common problem with Access)

myNewDate = cdate(format(DateIn,"mm/dd/yy"))

Answer 2

You have a few options, the most logical would be to simply convert the values in that column to the new format upon promotion from your production environment to your live environment.

However, I would rather stress that it is a best practice to have your test/development/qa environments as close to production as possible, therefore a re-configuration of your servers to all use the same format would help avoid the issue.

Answer 3

In insert/update queries Access expects dates in mm/dd/yyyy format regardless of the regional settings.

Answer 4

As a general rule, converting DateTime to/from string without specifying exact format is almost every time a bad idea.

Answer 5

Convert the date to an ISO Date format instead - YYYY-MM-DD. When you put it in that format, there is ZERO ambiguity, and MS's date functions always understands it.

Answer 6

this is what i do ALWAYS while working with datetimes in my front end: specify my own exact format (which is usually DD/MMM/YYYY - that way i am sure my day part is always numeric, month part string and year part a 4 digit number

also - USE DateTime.ParseExact instead of DateTime.Parse. this way there is no ambiguity with front end code.

1 more tip - if you are using c# 3.5, i would really recommend you to create an extension method on the datetime class where you actually HARDCODE your specific format for a given application, and then instead of supplying your format at 10 different places where you are converting, simply make a call on this extension method

this way if for any reason you want to change your format later, you just have to change 1 extension method :-)