What are some Date (& Time) format best practices when wanting to adhere to localization in a new application.
For example, what format do you use to write dates to the database.
How do you display the following to the user using their own locale settings once the date has been obtained from the database.
ShortDate LongDate DateTime
Any tips for punters appreciated.
Always store dates in UTC and when displaying them, calculate the local time based on the user's time zone (which you will have to ask for at some point and store).
DateTime2 (aka long date in MS SQL) is only going to be useful if you need that level of granularity or 10,000 year range. DateTime seems to be the norm. If you only need the date portion and can ignore the time, use the new Date datatype in SQL 2008. Ask your clients what granularity is necessary for the project.
UTC is the best way to store the date since your server or your client can be anywhere in the world (assuming a web-based deployment). Also, if you move your server or have a new co-located server you won't need to adjust for timezone since everyone is already running on UTC.
You should only convert from UTC to local time in the presentation later. If your client is using a web browser you can get the timezone offset from javascript. Then if you need that value on the server-side, either store the value in a cookie or in a hidden HTML field for easy access.
I've used a tweaked version of this javascript sample from CodeProject to do the same.