I still have problem of date format

[rick rick]

I am using MS Access as my data source. The date field
was originally set to dd/mm/yyyy format and the server of
regional setting as well. Recently I have changed both
date format to dd/mm/yyyy. When I try to input the data
using adodb.recordset, it still store the data fomrat to
mm/dd/yyyy. The weirdest part is the date format works
fine with 2 digits day but not single digit. For
example, it will work fine if I store 19/01/2003 but it
wouln't work with 09/01/2003. It will come out
01/09/2003. Why ?? I have used the SetLocale function,
but it didn't work either! Any experts can help?

[Don Grover]

Access stores the date as a double precision not as dd/mm/yyyy etc, the
decimal part is a long date and the remainder is a long time.
The date format in access is only the display date and has nothing to do
with the stored date.
The reason it stores 19/01/2003 the right way is it cant store interpreit
any other way, where as 09/01/2003 can be store either way so access swaps
it round.
Put your dates in access as yyyy/mm/dd and it will go in properly.

Regards
Don Grover





"rick rick" <anonymous@discussions.microsoft.com> wrote in message
news:0b2e01c3da8a$d8da40f0$a101280a@phx.gbl...

> I am using MS Access as my data source. The date field
> was originally set to dd/mm/yyyy format and the server of
> regional setting as well. Recently I have changed both
> date format to dd/mm/yyyy. When I try to input the data
> using adodb.recordset, it still store the data fomrat to
> mm/dd/yyyy. The weirdest part is the date format works
> fine with 2 digits day but not single digit. For
> example, it will work fine if I store 19/01/2003 but it
> wouln't work with 09/01/2003. It will come out
> 01/09/2003. Why ?? I have used the SetLocale function,
> but it didn't work either! Any experts can help?
>
>

[Aaron Bertrand - MVP]

Always use an ISO standard date format, like YYYY-MM-DD. Then there is no
confusion from software or people.

If you allow free text date entry, well, you'll always have this problem.
If you use dropdowns or a calendar control, however, you can avoid all the
ambiguity and prevent users from becoming frustrated.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"rick rick" <anonymous@discussions.microsoft.com> wrote in message
news:0b2e01c3da8a$d8da40f0$a101280a@phx.gbl...

> I am using MS Access as my data source. The date field
> was originally set to dd/mm/yyyy format and the server of
> regional setting as well. Recently I have changed both
> date format to dd/mm/yyyy. When I try to input the data
> using adodb.recordset, it still store the data fomrat to
> mm/dd/yyyy. The weirdest part is the date format works
> fine with 2 digits day but not single digit. For
> example, it will work fine if I store 19/01/2003 but it
> wouln't work with 09/01/2003. It will come out
> 01/09/2003. Why ?? I have used the SetLocale function,
> but it didn't work either! Any experts can help?
>
>

[rick rick]

It works fine now. Thanks for Araon and Don for your
advise. Many many thanks!