Problems with Dates

R

Roger Hembury

Hi

I have a slight problem saving dates to a table from some ASP code.

The SQL looks like this -

INSERT INTO SCHEDULE (dtDate, dtTime, sPerson, mEvent) values(
#10/01/05#,#08:00#,'Mr X','Dentist')

but in the table Access (have 2002 but database is in 2000 format) has
converted the date to 01/10/05 which is really really annoying and I can see
no reason as to why it is doing this.

Why is it doing this and is there a way of correcting this? I have checked
all the dates, keyboard and international settings and everything is set to
UK.

TIA

Roger
 
R

Rick Brandt

Roger Hembury said:
Hi

I have a slight problem saving dates to a table from some ASP code.

The SQL looks like this -

INSERT INTO SCHEDULE (dtDate, dtTime, sPerson, mEvent) values(
#10/01/05#,#08:00#,'Mr X','Dentist')

but in the table Access (have 2002 but database is in 2000 format) has
converted the date to 01/10/05 which is really really annoying and I can see
no reason as to why it is doing this.

Why is it doing this and is there a way of correcting this? I have checked all
the dates, keyboard and international settings and everything is set to UK.
Click to expand...

Hard-coded dates in Access SQL MUST be US format or ISO (yyyy-mm-dd) or a format
where alpha characters are used in the month.
 
L

Luiz Cláudio C. V. Rocha [MVP]

Hi Roger,

SQL queries read date format in "American English", I mean, month/day/year.

So, when you enter values(#10/01/05#,#08:00#,'Mr X','Dentist'), it will be
understood as 01/October instead of 10/January.

Luiz Cláudio
 
V

Van T. Dinh

For more info., see the long thread "Varying date formats" started about 16
hrs ago in the "Forms" newsgroup newsgroup.
 
R

Roger Hembury

Hi Van,

Thanks for pointing me in the right direction.

Looks like for SQL everything needs to be in US format. Strange way of doing
it but then every language / program has it own little quirks.

Thanks again

Roger
 
Top