We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

Casting in WHERE Clause[Answered] RSS

7 replies

Last post Nov 15, 2014 01:51 PM by KulerMaster

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
KulerMaster
Member

117 Points

511 Posts
Casting in WHERE Clause

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
- Reply
Rion William...
All-Star

114593 Points

18503 Posts

MVP
Re: Casting in WHERE Clause

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 :
```
SELECT Field1 
  FROM Table1 
 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 :
```
SELECT Field1 
  FROM Table1 
 WHERE CDate(date1) = CDate(?)
```
Blog | Twitter | Stack Overflow | LinkedIn
- Reply
oned_gk
All-Star

52793 Points

15764 Posts

Re: Casting in WHERE Clause

Nov 15, 2014 01:05 PM|oned_gk|LINK
Date only is integer value, try cint(datefield) = cint(?)

Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer
- Reply
KulerMaster
Member

117 Points

511 Posts

Re: Casting in WHERE Clause

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
- Reply
KulerMaster
Member

117 Points

511 Posts

Re: Casting in WHERE Clause

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." .
- Reply
Rion William...
All-Star

114593 Points

18503 Posts

MVP
Re: Casting in WHERE Clause

Nov 15, 2014 01:32 PM|Rion Williams|LINK
You may be able to try the DateValue() function :
```
SELECT Field1 
  FROM Table1 
 WHERE DateValue(date1) = DateValue(@Date1)
```
Or you could try something crazy like the following :
```
SELECT Field1 
  FROM Table1 
 WHERE CDate(Int(date1)) = CDate(Int(@Date1))
```
Blog | Twitter | Stack Overflow | LinkedIn
- Reply
KulerMaster
Member

117 Points

511 Posts

Re: Casting in WHERE Clause

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.

Thank you so much
- Reply
KulerMaster
Member

117 Points

511 Posts

Re: Casting in WHERE Clause

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