Casting in WHERE Clause [Answered]RSS

• • Reply
  KulerMaster

  Member

  113 Points

  491 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

  113820 Points

  18475 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

  52393 Points

  15613 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

  113 Points

  491 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

  113 Points

  491 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

  113820 Points

  18475 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

  113 Points

  491 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

  113 Points

  491 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