In a query withdateseg SELECT * FROM tblCustomers WHERE
HireDate <DateValue
can you use UK format for thedateof dd/mm/yyyy or does it have to be in
US format of mm/dd/yyyy.
Literal dates must be in US format. You can convert what the user enters in
accord with their computer's regional settings using the Format() function:
PARAMETERS [Enter cutoffdate:]DateTime;
SELECT * FROM tblCustomers WHERE [HireDate] < Format([Enter cutoffdate:],
"mm\/dd\/yyyy")
John, aren't you one of those who regularly remind us that DATETIME
values are stored as 1's and 0's on magnetic-coated aluminium (or
something, I try to forget) <g>?
Seriously now, isn't formatting a red herring here? Consider this:
PARAMETERS [Enter cutoff date:] DATETIME;
SELECT DATEPART('M', [Enter cutoff date:]) AS month_number
FROM AnyTable
At the parameter prompt I can enter "2007-03-01" (no quotes) and it
gets interpreted as a DATETIME. Therefore, your "Literal dates must be
in US format" appears to be a misstatement because ISO 8601 format is
valid for DATETIME parameters. Further, I can enter "01/03/2007" (no
quotes), being an ambiguous format, and because my regional settings
are for the UK the month is interpreted as March, so the query returns
3 for the month number. Ditto if I change the parameter type from
DATETIME to TEXT. So even if you'd said "Ambiguous literal dates in
text parameters are assumed to be in US format" this would also be
incorrect (I don't mean to twist you words, I'm just trying to come up
with ways to make your statement true for parameters but I cannot).
Regardless of the above, what's the thinking behind comparing a column
strongly-typed as DATETIME with a text value based on a parameter
strongly-typed as DATETIME formatted US-style? Why not just compare
the two strongly-typed DATETIME values?
I think the value should be 'unformatted' to DATETIME in the 'front
end' (e.g. using CDate) rather than trying to convert the DATETIME as
text in the 'back end' proc.
Jamie.
--
