Problems with dates

[Guest]

Hi,

In this instruction
INSERT INTO MyTable ( date1, date2 )
SELECT #27/07/2005# AS Expr1, #08/02/2005# AS Expr2;

The date2 appears changed, I mind the day for the month like 08/02/2005,
only occurs when day is bigger than month.

Somebody knows what is happening?

 

[Guest]

Could this be a regional setting (Control Panel -> Regional Settings)? Access
will auto-correct 27/07/2005 to 07/27/2005 if these are set to English
(United States); that is, if the system date format is d/m/yyyy instead of
m/d/yyyy.

 

[Guest]

I checked this and is set to "Español - Mexico", dd/MM/yyyy, I think this is
right,
but the problem persist.

 

[Guest]

OK. Next step. What are date1 and date2 in the table after the append?

 

[John Spencer (MVP)]

Literal dates in SQL must be expressed in US Format (mm/dd/yyyy) or a
non-ambiguous format - such as yyyy-mm-dd.

Try

INSERT INTO MyTable ( date1, date2 )
SELECT #2005-07-27# AS Expr1, #2005-08-02# AS Expr2;

Or alternatively

INSERT INTO MyTable ( date1, date2 )
SELECT #07/27/2005# AS Expr1, #02/08/2005# AS Expr2;

See International Dates in Access at:
http://users.bigpond.net.au/abrowne1/ser-36.html

 

[Guest]

Thanks, John. I stand corrected.

 

[Douglas J. Steele]

As John points out elsewhere in this thread, you must use mm/dd/yyyy format
when using the # delimiters.

If you think this might be confusing, you could use CDate('27/07/2005') and
CDate('08/02/2005'). Note the quotes within the expression: without them,
you'll get wrong values (as Access will do division on the numbers before
converting them to dates!). As well, this won't work for users who have
chosen a different regional setting.

For more information about working with International Dates, check Allen
Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Thank Douglas finally it works:

"#" & Format(Me!datStart, "mm\/dd\/yyyy") & "#"