Date Format

[shamas21]

Hi All

I have the following query that brings back data greater than 01/02/2008. However, its reading it in American Format i.e. mm/dd/yyyy

In my table I have it in the UK format i.e. dd/mm/yyyy. I want the query to return the results in?

I know that its easy just to change the query to American format, but i prefer to just type the UK format in and get the results back.

Thanks

SELECT productname, date
FROM orders
WHERE date > #01/02/2008#

 

[RuralGuy]

Just for the record, a DateTime field is *always* stored the same way.
http://support.microsoft.com/default.aspx/kb/q130514/
This link may help solve the problem: http://allenbrowne.com/ser-36.html

 

[jal]

Just for the record, a DateTime field is *always* stored the same way.
http://support.microsoft.com/default.aspx/kb/q130514/
This link may help solve the problem: http://allenbrowne.com/ser-36.html

The first article you gave says that the Control panel determines the date format. Does this mean that shamas' problem lies there?

 

[georgedwilkinson]

I'm pretty sure that's what he's saying. Access only stores dates one way. Access attempts to interpret dates if they are in a format other than the default. Shamas is having problems with typical "Americanized" dates behaving like American dates. It follows that shamas' date format is set to "American". Fix that (and any code that behaves that way) and the symptoms will go away.

 

[Jon K]

In query SQL View (as well as SQL statements in VBA), the date value has to be in American format #2/1/2008#

In query Design View, the date value follows the short date format set in the system's regional setting in the control panel.


So on a UK system with short date format of dd/mm/yyyy, you can simply type the following in the criteria for the date field in query Design View:-
>1/2/2008

and Access will change it to >#01/02/2008#, following the regional setting.
.