By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Problem with Format(Date) in SQL statement

P: n/a
Hello

I am having some problem comparing dates with the SQL statement below
MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as [Expr1] FROM TblDates WHERE
_
Format(Date, 'yyyy/mmmm/dd') = #2006/April/18#"

Even though the date 2006/April/18 is in TblDates, it is never found by the
above SQL statement

Anyone know why?
Thanks

G Gerard
Apr 29 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
G Gerard wrote:
Hello

I am having some problem comparing dates with the SQL statement below
MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as [Expr1] FROM TblDates WHERE
_
Format(Date, 'yyyy/mmmm/dd') = #2006/April/18#"

Even though the date 2006/April/18 is in TblDates, it is never found by the
above SQL statement

Anyone know why?


If the field in your table is named "Date" then that's probably the problem. "Date" is a
reserved word and should never be used as a field name. You might get this to work by
enclosing the field name in brackets but using "Date" as a field name is a no-no.

And you don't need to use format in the WHERE clause; that's just going to slow you down.

MySQL = "SELECT Format([Date], 'yyyy/mmmm/dd') as yourDateName FROM TblDates" & _
" WHERE [Date] = #4/18/2006#;"

but it would be better as:

MySQL = "SELECT Format([myDate], 'yyyy/mmmm/dd') as yourDateName FROM TblDates" & _
" WHERE myDate = #4/18/2006#;"
--
'---------------
'John Mishefske
'---------------
Apr 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.