How to get dates to display/store in dd/mm/yyyy format.

[mnongkhlaw]

I'm using Access 2000 with the input mask 00/00/0000;0;_ and short date format. British dates like 01/02/2003 (1st Feb, 2003)entered thru a web page's textbox bound to date/time field gets converted to 02/01/2003 in the table, but dates like 20/01/2003 (20th Jan, 2003) get stored correctly. My Regional Options (Win2000 server) settings :-

Locale : English (US) OR English (UK)
Short date format : dd/MM/yyyy

I found that entering 01/02/2003 directly into the Access table gets stored correctly. Then I just did a

Sub thisPage_onenter()
txtCustFrom.value=FormatDateTime(Date,vbShortDate)
End Sub

to find out whether my web page was the culprit, and sure enough, it displayed 10/23/2003 (American) instead of 23/10/2003 (British) in the txtCustFrom textbox bound to a date/time field.

How can I resolve it? I want to display and store dates in british format only.

Mark

 

[MerlinB]

The FormatDateTime is not very flexible! Your best bet is to use the many functions to get the various bits of date out:

dtToday = Date
strDate = Day(dtToday) & "/" & Month(dtToday) ..etc

if you wanted two digit day & month try:
.. Right("0" & Day(dtToday), 2) ..

ttfn

http://www.corporateinternet.com

 

[mnongkhlaw]

Thank you, MerlinB, but the code snippet I did was just to check whether it was because of Access or webpage. Assuming my regional settings are correct, the question that still remains is how to get my date to be displayed in British (dd/mm/yyyy) instead of American (mm/dd/yyyy) in my web page? Has it got anything to do with IIS5?

Mark

 

[MerlinB]

If you get the date from Access into a RecordsetDTC, then the value is already converted to a string (by the RecorsetDTC code). This has some nasty implications, esp. if the International Settings on the server is US not UK!

You could look through the RecordsetDTC code in the _ScriptLibrary (RECORDSET.ASP, not .JS), and add a new 'GetDate' method (or tweek the GetValue) to return a column as a date, not a string. You may need the following JScript (see the help text on getVarDate()):
Check the column type first:

var fieldType = this._rsFields(field).type;
//adDate, adDBDate
if ((fieldType == 7) || (fieldType == 133))
r = new Date(this._rsFields(field).value).getVarDate();
else...

Alternatively, you could try to format the date into day month and year parts in the SQL, and re-format them in your ASP code - or similar.

http://www.corporateinternet.com

 

[mnongkhlaw]

I tested this same page in Win98 & PWS where the regional settings are set for English (US),Short date format : dd/MM/yyyy. British-style dates entered into the textbox get stored correctly into my date/time field without doing anything else.

So, my guess is that the regional settings of the Win2000 server machine where the application is to be deployed might not have been configured properly.My Regional Options (Win2000 server) settings :-

Locale : English (US)
Short date format : dd/MM/yyyy
From Date/Time applet of control panel (Time Zone tab), I've set Time Zone to "Calcutta,Chennai,Mumbai,New Delhi".

Is there anything else I need to set?

Mark

 

[MerlinB]

Perhaps it is the regional settings of the web server login (IUSR_zzz) that is wrong.

The way that we overcame the problem is to ensure that the month is always presented as a short name (Jan Feb etc.)Just make sure the year is in full - so the year and day do not get confused!

To assist in this, we created a JavaScript pop-up calendar. Have a look at

http://www.corporateinternet.com/Timesheets

Select Admin Menu (this is just a demo system) and select Maintain Users. On this page there is a calendar.
You should be able to discover/download the js files via this page (look in the temp web directory, or view page source and type in the .js file name).

Both Access and SQLServer, and most other databases, understand months as short names.

************
PS...
The Timesheet system was developed in VI using DTCs - somewhat tweeked by me to give sub-totals in grids etc.

Then .Net came along - and though .Net uses the same ideas, there is no direct migration from DTCs to .Net. I'll have to re-code the whole d**n lot - and improve it somewhat along the way.

http://www.corporateinternet.com

 

[mnongkhlaw]

I visited the site, but couldn't get to download the JS files. Would be greatly obliged if you could send them to me (mark@shillong.meg.nic.in).

However, please also explain how can I change the regional settings of IUSR_myserver. Perhaps that will do the trick.


Mark

 

[mnongkhlaw]

I got your JS code, thank you.

Your suggestion apparently worked!
I just fed in dates like 1-Mar-03 etc. in my page, and it stored correctly (in british format in my table-like 01/03/2003)!

However, the next time the page was viewed, the dates reverted back to American! (like 03/01/2003 in the above example). This will be confusing for updation purposes.

Can you also explain how can I change the regional settings of IUSR_myserver. I wish to try that too. Will the regional settings of my clients' computers (those browsing the site) also affect the way the dates are stored and displayed?

Mark