Hi, I want to populate a date field in a .asp page and then save the date 

in a access database. 

The date input format is DD/MM/YYYY. I did find out that if the Day is 

less than 13 it takes it as Month instead of Day.

For example if I write 07/10/2001, the result in with a request is 

10/07/2001. If I write 10/07/2001 I get 07/10/2001 instead. 

If I write 22/04/2001 I get back the right date. 

The database itself saves the dates in the format MM/DD/YYYY so I use a 

function to get back the right format, but it works only with dates 

containing a day number greater than 12.

Any idea on that issue?

Many thanks

Valentino

Access stores the date in the Database in the mm/dd/yyyy format.



To store it, simply insert it as normal, access flips it, and stores it.

When reading it, access flips it back dependant on your System Locale.



So carry on as normal. The fun starts when you need to search on the

database for specific dates. Here you need to flip the Search date before

placing it into your SELECT query. Access then finds your data and flips the

output for you.



Annoying or what!



To flip the date, simply use this command



<% strDate = Month(date) & "/" & Day(Date) & "/" & Year(date) %>





Hope this helps.



Craig.





-----Original Message-----

From: Valentino Osterwalder [mailto:valentino@t...]

Sent: Monday 15 October 2001 15:55

To: ASP Databases

Subject: [asp_databases] Saving a date from asp page to access database





Hi, I want to populate a date field in a .asp page and then save the date

in a access database.

The date input format is DD/MM/YYYY. I did find out that if the Day is

less than 13 it takes it as Month instead of Day.

For example if I write 07/10/2001, the result in with a request is

10/07/2001. If I write 10/07/2001 I get 07/10/2001 instead.

If I write 22/04/2001 I get back the right date.

The database itself saves the dates in the format MM/DD/YYYY so I use a

function to get back the right format, but it works only with dates

containing a day number greater than 12.

Any idea on that issue?

Many thanks

Valentino

No, this type of chicanery is not required.



Firstly, Access stores dates as a number, just like any other database. The

problem occurs when inserting a date into Access. if the date can be

construed in US format as a valid date, then it will be so construed. Only

if the date can be interpreted as a US date will other formats be tried.



The simple solution is to use a non-ambiguous format, eg (my preference) ISO

format: yyyy/mm/dd



If all your queries, (SELECT, INSERT) etc use ISO format, then you'll never

have any problems.

A simple date formatting function, ala,

http://www.4guysfromrolla.com/webtech/022701-1.shtml can be used to generate

this format from your existing dates if you're too laz...I mean "too busy"

to write your own :-)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Craig Flannigan" <ckf@k...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, October 16, 2001 1:16 AM

Subject: [asp_databases] RE: Saving a date from asp page to access database





: Access stores the date in the Database in the mm/dd/yyyy format.

:

: To store it, simply insert it as normal, access flips it, and stores it.

: When reading it, access flips it back dependant on your System Locale.

:

: So carry on as normal. The fun starts when you need to search on the

: database for specific dates. Here you need to flip the Search date before

: placing it into your SELECT query. Access then finds your data and flips

the

: output for you.

:

: Annoying or what!

:

: To flip the date, simply use this command

:

: <% strDate = Month(date) & "/" & Day(Date) & "/" & Year(date) %>

:

:

: Hope this helps.

:

: Craig.

:

:

: -----Original Message-----

: From: Valentino Osterwalder [mailto:valentino@t...]

: Sent: Monday 15 October 2001 15:55

: To: ASP Databases

: Subject: [asp_databases] Saving a date from asp page to access database

:

:

: Hi, I want to populate a date field in a .asp page and then save the date

: in a access database.

: The date input format is DD/MM/YYYY. I did find out that if the Day is

: less than 13 it takes it as Month instead of Day.

: For example if I write 07/10/2001, the result in with a request is

: 10/07/2001. If I write 10/07/2001 I get 07/10/2001 instead.

: If I write 22/04/2001 I get back the right date.

: The database itself saves the dates in the format MM/DD/YYYY so I use a

: function to get back the right format, but it works only with dates

: containing a day number greater than 12.

: Any idea on that issue?

: Many thanks

: Valentino