A problem with dates

[Sproul]

when i'm out on a date....... Just kidding

frontpage sends a timestamp to my database which is in the format of
dd/mm/yyyy
for my query field to work it has to be in the format of mm/dd/yyyy.

the results do work, but being in the UK we prefer to use dd/mm/yyyy

how do i change my query field to work with dd/mm/yyyy, also is it possible
to force my field to only accept date format.


Al....

 

[Kevin Spencer]

Hi Sproul,

It sounds like you're using a text data type to store a date. Is that
correct? That might explain the problem you've been having with < and >
operators, which also work on strings.

The proper way to store a date is as a date data type. The format is only
Access-interface-specific. In fact, since the Y2K issue, date data types are
stored as pure numbers, usually indicating the number of ticks
(100-nanosecond intervals) or milliseconds that have elapsed since 1/1/1900.
These numbers are stored in any of several ways, though, and may be a
combination of a number of years, months, days, hours, minutes, seconds,
milliseconds, etc., depending on the platform. In any case, the important
thing to remember is that the format you see in Access is formatted for your
convenience, and does not reflect the actual value of the data stored, which
is in binary numeric format that the computer understands.

When you store a date value as a date, you can perform various date-time
functions on it for comparison, addition, subtraction, etc. The computer
understands what the date is, regardless of the locale of the computer. The
date value is locale-agnostic, other than the time zone, which may or may
not come into play.

If you store the date as a date, and use the same syntax, the query will
work correctly. In addition, you can format the results in your web page in
any format you choose.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

 

[Sproul]

i've double checked the field in the access database, it is infact a
Date/Time field and not text, when i query results it comes back in the
format of dd/mm/yyyy.

but to query the field from the web the input field required the date to be
put in mm/dd/yyyy.

at the moment i only have records for 12th and 13th August 2005

if i search "start field" 10/08/2005 and "finish field" 15/08/2005 no
records are returned.

however if i change the dates to 08/10/2005 and 08/15/2005 i get results back

therefore i think it's how the webpage or "input field" translates the date
and not ACCESS, which is why i would like to know if you can force the field
to accept the date in a particular format and also only accept dates,
i know you can make the field only accept 10 character and also only accept
0123456789/, but that still means you can write 0123456789 which is obviously
not in a date format.

Al....

PS just for the record I do pick my nose and dont hide it.

 

[Kevin Spencer]

In VBScript, which is the language that ASP uses by default, Dates are
formatted as strings using the Regional Settings of the local computer (the
server on which the ASP app resides). You can change the Locale used by ASP
using the VBScript SetLocale() function. Read about it here:

http://msdn.microsoft.com/library/en-us/script56/html/vsfctSetLocale.asp?frame=true

In addition, you can use the VBScript FormatDateTime() function to have a
bit more control over the format that a Date is displayed in. You can read
about it here:

http://msdn.microsoft.com/library/en-us/script56/html/vsfctFormatDateTime.asp?frame=true

Note that this will require a bit of customization of your
FrontPage-generated ASP code.

Also note that these 2 pages are part of the Microsoft Visual Basic
Scripting Edition online reference, which you may want to bookmark for
future reference:

http://msdn.microsoft.com/library/en-us/script56/html/vtoriVBScript.asp?frame=true

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

 

[p c]

As Kevin said, you can set the local date format for the machine. Also,
if you open the Access dbase, you can set the date format you want to so
that the display and the query results will be in the UK format.

In addition to that or as an alternative, you can use the format date
functions suggested by Kevin for formatting the query prior to
submission to correspond to the the default format of the database
and/or to format the results that arrive from the query to correspond to
the format you want to display on th web page.

One way to ensure that dates submitted by the query are the intreptted
correctly by the DBase is to use a function to convert submitted date to
ISO date dorma. This way the DB will not get confused regardless of the
date format in use.

ISO Format is like this: yyyy-mm-dd.

For sample functions, gogle search for "ASP function ISO date"
...Pc