CODEWITHSUNDEEP
sqlms-accessms-access-2010
Nelman Jay Louie Vasquez
Nelman Jay Louie Vasquez

Reputation: 195

MS Access SQL - Compare General Date to Short Date

I need to compare the 2 different date types (General Date & Short Date).

I tried to use FORMAT() to able to get the converted date, but you can't use it in WHERE Clause.

"SELECT DT, Item, N, InvoiceNum, FORMAT(DT, 'Short Date') as DT_SD FROM itemHistory WHERE DT_SD='#" & selectedDate & "#')"

Upvotes: 0

Views: 104

Answers (2)

dwir182
dwir182

Reputation: 1549

You can't use Alias in your where because your alias use in your select. Unless you wrap in to subquery

Reason :

Where : conditions are evaluated and rows that do not match are removed

So your where statement see as your row not your aliasing. Use the full expression instead as stated by @Rene.

"SELECT DT, Item, N, InvoiceNum FROM itemHistory 
WHERE FORMAT(DT, 'Short Date') = #" & selectedDate & "#)"

Upvotes: 1

Gustav
Gustav

Reputation: 55906

You should always handle dates as dates, not text.

For Access SQL, you will have to use a properly formatted string expression for the date values:

"SELECT DT, Item, N, InvoiceNum FROM itemHistory WHERE DateDiff('d', DT, #" & Format(selectedDate, "yyyy\/mm\/dd") & "#) = 0"

If DT never contains a time value, this can be reduced to:

"SELECT DT, Item, N, InvoiceNum FROM itemHistory WHERE DT = #" & Format(selectedDate, "yyyy\/mm\/dd") & "#"

This will run faster, if you have an index on field DT.

Upvotes: 0

Related Questions