UK/US Format Data Entry and DataBase Storage

[JP SIngh]

Hi All

We are creating a multi-region ASP application which will be using SQL
Server 2000.

As our users exist in multiple location i.e. UK, US, Australia how can we
distinguish that the date the user is entering is captured correctly i.e. no
confusion with DD/MM/YYYY format.

I guess to remove database ambiguity I am going to use the format YYYYMMDD
format but how do I know in the first place when the user entered 01/06/2006
that the user meant 1st June 2006 or 6th Jan 2006.

Any help is appreciated.

 

[Bobbo]

I guess to remove database ambiguity I am going to use the format YYYYMMDD
format but how do I know in the first place when the user entered 01/06/2006
that the user meant 1st June 2006 or 6th Jan 2006.

I always store dates in the format yyyy-mm-dd hh:mm:ss.mmm as it makes
more logical sense (largest ---> smallest) and -- as you point out --
removes any regional discrepancies.

Take a look at this:
http://www.aspfaq.com/show.asp?id=2040

 

[Bob Barrows [MVP]]

Hi All

We are creating a multi-region ASP application which will be using SQL
Server 2000.

As our users exist in multiple location i.e. UK, US, Australia how
can we distinguish that the date the user is entering is captured
correctly i.e. no confusion with DD/MM/YYYY format.

I guess to remove database ambiguity I am going to use the format
YYYYMMDD format but how do I know in the first place when the user
entered 01/06/2006 that the user meant 1st June 2006 or 6th Jan 2006.

You can tell them to enter the data in the proper format, or use one of the
many javascript calendar controls which can be found via a google search.
SQL Server will not store any format with the dates it stores. A datetime
column stores dates as paired integers, the first of which containing the
number of days since the seed date, and the second of which containing the
number of milliseconds since midnight for the time portion. If no date is
supplied (time only) then 0, or the seed date will be stored. If a date with
no time is supplied, then 0, or midnight, will be stored.

It is up to the application that is retrieving the data to apply the proper
date format to the datetimes. vbscript has some date formatting capability.
You can also use the T-SQL CONVERT() function to convert the datetime to a
string containing the date with the desired format (it's easy if you
remember that strings contain format: other datatypes rarely contain format)