I'm really struggling with this. I've had a script running for ages now, 
part of which is writing a date to an Access database with a SQL string. 
SQL string is:
 
SQLString = "INSERT INTO chat (f_date) VALUES (#" & Date & "#)"

From 13/07/2002 onwards (yes - I'm using British date/time format on a 
British server) my database or SQL queries have started screwing up. It 
isn't affecting the running of the scripts, but it's very weird.
 
The date was being stored fine in the database as 12/07/2002 but on the 
13th it flipped to storing 02/07/2013, 14th became 02/07/2014, etc. - it's 
flipping the year and the month around. Now instead of storing DD/MM/YYYY 
it's storing YY/MM/YYDD!
 
Oddly enough, the database is still returning data in the right format to 
the browser in ASP - just storing it wrong. Also, I have other 
applications running on the same and other servers using the same code and 
they're fine.
 
If it started flipping the DAY and the month around I'd expect that 
(American format date issue or something), but YEAR and month?! Any ideas 
anybody? Please!

Greg.

Not so "tricky" if you know where to look:

http://www.adopenstatic.com/faq/DateswithAccess.asp

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <greg.harvey@w...>
Subject: [access_asp] A tricky one!


: I'm really struggling with this. I've had a script running for ages now,
: part of which is writing a date to an Access database with a SQL string.
: SQL string is:
:
: SQLString = "INSERT INTO chat (f_date) VALUES (#" & Date & "#)"
:
: From 13/07/2002 onwards (yes - I'm using British date/time format on a
: British server) my database or SQL queries have started screwing up. It
: isn't affecting the running of the scripts, but it's very weird.
:
: The date was being stored fine in the database as 12/07/2002 but on the
: 13th it flipped to storing 02/07/2013, 14th became 02/07/2014, etc. - it's
: flipping the year and the month around. Now instead of storing DD/MM/YYYY
: it's storing YY/MM/YYDD!
:
: Oddly enough, the database is still returning data in the right format to
: the browser in ASP - just storing it wrong. Also, I have other
: applications running on the same and other servers using the same code and
: they're fine.
:
: If it started flipping the DAY and the month around I'd expect that
: (American format date issue or something), but YEAR and month?! Any ideas
: anybody? Please!

Greg,

Access tries to make sense of the date it is given.  The way around this 
is to present the date in the iso standard date format YYYY-MM-DD. I had 
this problem recently and got round it with the following function.

Regards,
Paul
paul@e...

' This function formats the date to iso standard yyyy-mm-dd format so the 
dates
' cannot be misinterpreted by Access
Function f_format_iso_date( pstrDate )
        dim strDate
	dim strDay
	dim strMonth
	dim strYear
	dim strConvertedDate
		
	strDate		= FormatDateTime( pstrDate, vbShortDate )	
	strDay		= day( strDate )
	if len( strDay ) = 1 then
		strDay = "0" & strDay
	end if		
	strMonth	= month( strDate )
	if len( strMonth ) = 1 then
		strMonth = "0" & strMonth
	end if				
	strYear		= year( strDate )	
		
	strConvertedDate = strYear & "-" & strMonth & "-" & strDay
		
	f_format_iso_date = strConvertedDate
End Function


> I'm really struggling with this. I've had a script running for ages now, 
p> art of which is writing a date to an Access database with a SQL string. 
S> QL string is:
 > 
S> QLString = "INSERT INTO chat (f_date) VALUES (#" & Date & "#)"

> From 13/07/2002 onwards (yes - I'm using British date/time format on a 
B> ritish server) my database or SQL queries have started screwing up. It 
i> sn't affecting the running of the scripts, but it's very weird.