Last post Nov 15, 2014 01:51 PM by KulerMaster
Nov 15, 2014 12:44 PM|KulerMaster|LINK
In SQL Server when i need to select data of the certain date (day) i usually use the following query casting the date field and the param:
SELECT Field1 FROM Table1 WHERE CAST(date1 AS date) = CAST(@Date1 AS date)
How do i do the same in a saved query in MS Access? If i just say "WHERE date1 = DateTimeParam" it returns no data from that day.
Thank you so much
Nov 15, 2014 12:56 PM|Rion Williams|LINK
I'm memory serves, I believe the appropriate syntax for casting as a Date in Access uses the
CDate() function :
WHERE CDate(date1) = CDate(@Date1)
I don't remember how Access handles parameters, but they may or may not be named. You might try using '?' characters to denote them if the above approach doesn't like the parameterization :
WHERE CDate(date1) = CDate(?)
Nov 15, 2014 01:05 PM|oned_gk|LINK
Nov 15, 2014 01:18 PM|KulerMaster|LINK
Thank you for replying (it's much appreciated)
Btw this is a SAVED QUERY (Inside MS Access database) not a query contained in the C# or VB code
However CDate contains the TIME portion as well and therefore it never matches :(
Isn't there a way to cast the datetime to ONLY DATE and then compare?
Thank you again
Nov 15, 2014 01:19 PM|KulerMaster|LINK
Thank you for replying it's much appreciated.
Unfortunately if i use Cint function I get an error "Data type mismatch in criteria expression." .
Nov 15, 2014 01:32 PM|Rion Williams|LINK
You may be able to try the
DateValue() function :
WHERE DateValue(date1) = DateValue(@Date1)
Or you could try something crazy like the following :
WHERE CDate(Int(date1)) = CDate(Int(@Date1))
Nov 15, 2014 01:49 PM|KulerMaster|LINK
DateValue worked .. will test the other solution shortly and will let you know if it works as well.
Nov 15, 2014 01:51 PM|KulerMaster|LINK
Nah the other one throws an exception "Data type mismatch in criteria expression."
Anyway thanks for the DateValue suggestion