Format input date for passing to database

[Holdstrong]

Hi there, I have done a few hours worth of searches on this and have spent the better part of tonight reading other threads about it... but yet the problem is still unclear to me.

I am hoping if I present my specific issue that someone can offer a solution or explanation in a way that clicks with me.

My site is being hosted by a third party. I have a form that requests a date from the user and then stores that date in an Access Database. I live in North America and the users always enter the date mm/dd/yyyy and it has always been stored in the Access Db that way and has always been recalled from the Access Db for display without issue.

Until last week.

I am not sure if my host changed something or what happened, but now when my users enter a date into the form (mm/dd/yyyy) it is being stored, and subsequently displayed, in the Access Db as dd/mm/yyyy instead.

I'm using very simple code to pass the info to the Db:

objrs.fields("d ate") = request.form("m ydate1")
objrs.fields("e nd_date") = request.form("e nddate")

With the form code looking like this:

<input type=text name="mydate1" size=12 value="<%= Date() %>" class="9pxTextC opy">

<input type=text name="enddate" size=12 class="9pxTextC opy">


From the extensive reading I have done today it looks like my Server is now storing the dates sent to it in a format other than standard North America. Is there a way to make the Db store the dates the way it is receiving them?

[MMcCarthy]

Have a look at the following tutorial and see if it helps.

Literal DateTimes and Their Delimiters (#).

If not come back and we'll try something else.

Mary

[Holdstrong]

Have a look at the following tutorial and see if it helps.

http://www.thescripts. com/forum/showthread.php? threadid=575420

If not come back and we'll try something else.

Mary

Hi Mary, thanks for the response.

The tutorial you linked may or may not help... but I am pretty new to this and I really didn't understand what it was trying to teach me. It looked like it was telling me to use m/d/y for my strings and saying that you can format() a string before passing it to the Database?

It wasn't clear to me and I didn't see any examples. But my strings "mydate1" and "enddate" from the form on my page are being entered by the user in a 2/7/2007 format (for today's date)

Do I still need to format them in someway even if they are being entered in the correct format?

[MMcCarthy]

Hi Mary, thanks for the response.

The tutorial you linked may or may not help... but I am pretty new to this and I really didn't understand what it was trying to teach me. It looked like it was telling me to use m/d/y for my strings and saying that you can format() a string before passing it to the Database?

It wasn't clear to me and I didn't see any examples. But my strings "mydate1" and "enddate" from the form on my page are being entered by the user in a 2/7/2007 format (for today's date)

Do I still need to format them in someway even if they are being entered in the correct format?

Access can have problems processing dates passed in this way. The problem is not how they are formatted on the web page but how the data received by Access is being interpreted by the Jet Engine.

As a test send the date to a test field that has a text format. This should show you how the data is being passed. If the text shows the correct format then your problem lies in the Jet Engines interpretation of that. If this is in fact the case then in the field that holds the date in the datebase use a format like mm/dd/yyyy rather than using the access default format for this date.

Let me know how you get on.

Mary

[NeoPa]

A complicated issue to understand.
A couple of points that may make comprehension a little easier :

1. Dates are not stored in any format other than decimal numbers.
2. The Date part of a Date/Time field is integral
3. The time part is a fraction of a day and is stored that way.
4. The issue of dates appearing as strings of the wrong format is about how a date is formatted for viewing.
5. A date string can appear as a date but is fundamentally different - one is a Date and the other a string.
6. The tutorial is referring to problems handling dates within SQL code specifically. It is therefore unlikely to affect your issue directly (although it may well help you to appreciate the concepts involved).

To get to the route of your issue you need to understand exactly what is passed to where in the structure of your application. Finding out if it is the Access process which passes the date as an (incorrectly) formatted string to the next stage or whether it is the next stage itself which is misinterpreting it is a very important step. I would guess that one of the machines has been set up with British Regional settings, as we typically use the DD/MM/YYYY format over here.

[Holdstrong]

A complicated issue to understand. . .

To get to the route of your issue you need to understand exactly what is passed to where in the structure of your application. Finding out if it is the Access process which passes the date as an (incorrectly) formatted string to the next stage or whether it is the next stage itself which is misinterpreting it is a very important step. I would guess that one of the machines has been set up with British Regional settings, as we typically use the DD/MM/YYYY format over here.

Thanks for the follow ups. Great troubleshooting step by the way.

Ok so I created a test field in my Access Db and made sure it was set to text. I then altered my code slightly to pass the "mydate1" value from the user form to that text field instead of the usual Date/Time field and viola!

2/7/2007 is properly received and stored in that "testtext" field as the text 2/7/2007

Whereas "enddate", which is sent to the Date/Time field, gets received and stored as 7/2/2007

Ok, so I think we can say it is being passed along properly from the ASP page.

Here are the results when I send "mydate1" to "texttest" instead of "date" and when I send "enddate" to its original Date/Time field "end_date" via:

objrs.fields("textest") = request.form("mydate1")
    objrs.fields("end_date") = request.form("enddate")

ID = 6035
name = testing
date =
end_date = 7/2/2007
textest = 2/7/2007
note = testing
employeeID = 13

[NeoPa]

Unfortunately, you've only tested the difference between a date and a text field.
As both ends know that you're dealing with text neither would attempt to translate. As for the date, it was translated, but by which system.
I don't know the details of your setup, so I can only offer general advice.

1. Check the Regional Settings on all relevant PCs.
2. When doing any tests on date fields always use unambiguous date strings (EG 1 Jan 2007) for your test data.

[Holdstrong]

Unfortunately, you've only tested the difference between a date and a text field.
As both ends know that you're dealing with text neither would attempt to translate. As for the date, it was translated, but by which system.
I don't know the details of your setup, so I can only offer general advice.

1. Check the Regional Settings on all relevant PCs.
2. When doing any tests on date fields always use unambiguous date strings (EG 1 Jan 2007) for your test data.

Thanks again!

#1) The site is hosted by a third party company, so I suppose I can call them up and work through their tech support chain to try to get them to test out all of the machines relevant to our site. I have the feeling this might be a fruitless adventure but I will try

#2) If I send an unambiguous date string such as 7 Feb 2007 it is in fact translated correctly. So when I type in 7 Feb 2007 in both the "mydate1" and the "enddate" form field and send them to the same Access fields as mentioned before the new results are:

ID = 6036
name = testing
date =
end_date = 2/7/2007
textest = 2/7/2007
note = testing
employeeID = 13

[Holdstrong]

I may be getting ahead of myself here, but to recap:


Access is converting dates I send to it in the mm/dd/yyyy format and is storing them as dd/mm/yyyy.

However, if I send it an unambiguous date, such as 7-feb-2007 (dd/mmm/yyyy) or 2007/2/7 (yyyy/mm/dd) Access does receive and store the info correctly.

This appears to be a problem with a regional setting somewhere in my hosting solution. Since this is a third party hosting solution I have low hopes of them resolving it (but I am in contact with them)

It looks like another option would be to force people to insert the date in an unambiguous format... but, as we all know, relying on your users to do something, especially something which will feel foreign to them, is unlikely to produce happy results. However, what if there was a way for me to take the values entered in those forms and force a format change behind the scenes BEFORE it gets passed to Access?

In other words, my user enters 2/7/2007 in my form. That value is saved as "mydate1" via simple form HTML:

<input type=text name="mydate1" size=12 class="9pxTextCopy">

and then is passed along to he database as is via:

objrs.fields("mydate1") = request.form("mydate1")

But what if before passing along to the database I converted "mydate1" somehow... maybe using FormatDateTime( )??

Here is where my extreme noobness will shine. I have no idea how I would take a value from a Form ("mydate1") and run it through something like FormateDateTime before sending it off to the Db.

[MMcCarthy]

I may be getting ahead of myself here, but to recap:


Access is converting dates I send to it in the mm/dd/yyyy format and is storing them as dd/mm/yyyy.

However, if I send it an unambiguous date, such as 7-feb-2007 (dd/mmm/yyyy) or 2007/2/7 (yyyy/mm/dd) Access does receive and store the info correctly.

This appears to be a problem with a regional setting somewhere in my hosting solution. Since this is a third party hosting solution I have low hopes of them resolving it (but I am in contact with them)

It looks like another option would be to force people to insert the date in an unambiguous format... but, as we all know, relying on your users to do something, especially something which will feel foreign to them, is unlikely to produce happy results. However, what if there was a way for me to take the values entered in those forms and force a format change behind the scenes BEFORE it gets passed to Access?

In other words, my user enters 2/7/2007 in my form. That value is saved as "mydate1" via simple form HTML:

<input type=text name="mydate1" size=12 class="9pxTextCopy">

and then is passed along to he database as is via:

objrs.fields("mydate1") = request.form("mydate1")

But what if before passing along to the database I converted "mydate1" somehow... maybe using FormatDateTime( )??

Here is where my extreme noobness will shine. I have no idea how I would take a value from a Form ("mydate1") and run it through something like FormateDateTime before sending it off to the Db.

I'll post a pointer question in HTMS/CSS and see if anyone can help.

Mary