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("date") = request.form("mydate1")
objrs.fields("end_date") = request.form("enddate")

With the form code looking like this:

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

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


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

[AricC]

Why don't you use combo boxes to obtain the date that way you can get the date in whatever form you want. From your example you are using today's date, is this always the case? You don't need input from a user to get the current date.


HTH,
Aric

[AricC]

OK just read the entire thread. Are you using classic ASP or .Net? I would suggest using a Date/Time field in the DB not text. Below is some code to format the date/time with ASP:

Using VBScript

<script language="VBScript" runat="Server">
Dim dtmTodaysDate
 
dtmTodaysDate = Date
</script>
 

Using javascript:

<script language="Javascript">
    function GetTodaysDate()
    {
    var dtmToday = new Date();
    var strMonth = (dtmToday.getMonth() + 1) + "/";
    var strDay = dtmToday.getDate() + "/";
    var strYear = dtmToday.getFullYear();
    var strTodaysDateInProperDatabaseFormat = strMonth + strDay + strYear;
 
    document.write(strTodaysDateInProperDatabaseFormat);
    }
 
</script>
 

HTH,
Aric

[Holdstrong]

Why don't you use combo boxes to obtain the date that way you can get the date in whatever form you want. From your example you are using today's date, is this always the case? You don't need input from a user to get the current date.

HTH,
Aric

Hi there!

I am just using today's date as an example. This is an app where users will enter dates into a form, so those dates change and are input by the user. (it is a calendar app where people enter the dates they will be out of the office)

Unfortunately my coding knowledge is limited so I just had to look up what combo boxes were, but it doesn't seem like that would solve the issue. It has also been suggested that I could create separate form entries for Day, and Month, and Year and then pass them along that way. Sounds like that might solve the problem, but it would probably require a substantial re-write of other pages as well... and given my abilities...

Seems like the best option is try to convert the user entered data and then send it. So if someone enters 2/7/2007, I convert it to something unambiguous like 2007/2/7 and then send it to Access. Problem is I have no idea how to do that. It would involve taking form data and manipulating it on submit before passing off.

[Holdstrong]

OK just read the entire thread. Are you using classic ASP or .Net? I would suggest using a Date/Time field in the DB not text. Below is some code to format the date/time with ASP:

Using VBScript

<script language="VBScript" runat="Server">
Dim dtmTodaysDate
 
dtmTodaysDate = Date
</script>
 

Using javascript:

<script language="Javascript">
    function GetTodaysDate()
    {
    var dtmToday = new Date();
    var strMonth = (dtmToday.getMonth() + 1) + "/";
    var strDay = dtmToday.getDate() + "/";
    var strYear = dtmToday.getFullYear();
    var strTodaysDateInProperDatabaseFormat = strMonth + strDay + strYear;
 
    document.write(strTodaysDateInProperDatabaseFormat);
    }
 
</script>
 

HTH,
Aric

Definitely getting close here, I can taste it! Hahaha.

I am using a Date/Time field in the Db, we just used a text field to test something out.

So, the code you supplied above would seem to solve the problem for something like today's date... is there anyway to do that to the info being input by the user into the form (which is not always today's date)

Maybe run a function onsubmit that converts "mydate1" and "enddate"?

[MMcCarthy]

Hi there!

I am just using today's date as an example. This is an app where users will enter dates into a form, so those dates change and are input by the user. (it is a calendar app where people enter the dates they will be out of the office)

Unfortunately my coding knowledge is limited so I just had to look up what combo boxes were, but it doesn't seem like that would solve the issue. It has also been suggested that I could create separate form entries for Day, and Month, and Year and then pass them along that way. Sounds like that might solve the problem, but it would probably require a substantial re-write of other pages as well... and given my abilities...

Seems like the best option is try to convert the user entered data and then send it. So if someone enters 2/7/2007, I convert it to something unambiguous like 2007/2/7 and then send it to Access. Problem is I have no idea how to do that. It would involve taking form data and manipulating it on submit before passing off.

There are a couple of posts in the pointer thread that you might want to check out.

Formatting Inputed Date

[AricC]

I really think the combo boxes would be the best way for you to get your data in the correct format. If you need some help why not go post over in the ASP forum (Classic or .Net) we would be happy :) to help you with this over there.

Aric

[MMcCarthy]

I really think the combo boxes would be the best way for you to get your data in the correct format. If you need some help why not go post over in the ASP forum (Classic or .Net) we would be happy :) to help you with this over there.

Aric

If you like Holdstrong I can transfer this whole thread accross for you. Let me know.

Mary

[Holdstrong]

Yeah, I think this has definitely gone from an Access issue, to an ASP issue.

[NeoPa]

Yeah, I think this has definitely gone from an Access issue, to an ASP issue.

I'll do that for you now.
If I only understood the other side I'm sure we could get your issue sorted.
Just as some reading up material, specifically on the Access side of things, check out (Literal DateTimes and Their Delimiters (#).).
And for a little more general background info (Quotes (') and Double-Quotes (") - Where and When to use them.).

[MMcCarthy]

This question has been moved over from the access forum as the question has become an ASP issue.

Mary

[Holdstrong]

This question has been moved over from the access forum as the question has become an ASP issue.

Mary

Thanks for the move :)

I am going to set up a replicate page without our login procedures to continue to work on this today and hopefully give potential helpers an actual working example to see.

[Holdstrong]

Ok, my brain is slightly fried today and I have to leave this to work on other stuff now, but I think I have a test page up which at least demonstrates that the ASP page is in fact translating dates correctly and that it IS possible to convert the dates entered into the form before passing them on.

Here is the page: Test Page

I used the suggested correctDate code supplied in the linked thread.

Now, unless I am totally thinking about this wrong, all I need to do is figure out how to send the correctDate to the database instead of the forms "mydate1"

[Holdstrong]

Thanks for the help. I certainly have a much better handle of the problem now thanks to a lot of you.

Here was my solution.

I run the following code at the top of my page (thanks to person who posted this in the linked thread):

correctDate = day(request("mydate1")) & "-" & monthName(month(request("mydate1"))) & "-" & year(request("mydate1"))
correctEnddate = day(request("enddate")) & "-" & monthName(month(request("enddate"))) & "-" & year(request("enddate"))

And then, change this:

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

To this:

objrs.fields("date") = correctDate
objrs.fields("end_date") = correctEnddate

And bam, I am now converting the US Standard mm/dd/yyyy that my users enter to the semi-unambiguous dd/mmm/yyyy and sending THAT along to my Db instead. The Db is accepting it correctly and stores and then displays it correctly.

[MMcCarthy]

Glad you got it to work out and thanks for posting the solution.

Mary