 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / May 2007Using a column name in a where statement
I am attempting to refine down a date field to just the year to match it up with an argument, but it is coming up as an error. I tried: WHERE #DateFormat(datePosted, 'yyyy')# But it keeps telling me that the variable 'datePosted' is undefined I know the query works, but its attempting to refine the output result is giving me a problem. The query is: SELECT docId, title, docName, format, datePosted, category FROM AlumniDocuments WHERE #DateFormat(datePosted, 'yyyy')# = #arguments.year# AND CatOrder = 1 ORDER by title DESC Thanks for any help., It looks like you are attempting to use a CF function against a database column within the WHERE clause of a query, which really won't work. You should use the applicable database function for your particular database to extract the year from the date column. In Oracle, it would be TO_CHAR(datePosted, 'YYYY') and in SQL Server it would be something like datepart('yyyy', datePosted), etc. Phil Thanks Phil, that worked fine. Until I noticed that the user added all the documents this year and actually wants them sorted by the first 4 letters of the title (which happen to be a date). I need to actually preform what equates to a #left(datePosted,4)#. Any ideas on how to do that? > I need to actually preform what equates to a #left(datePosted,4)#. > Any ideas on how to do that? All the Database Management Systems that I have worked with have an equivalent function, they very by DBMS, but it should not be hard to find in the documentation and|or on line. Something like this? ORDER BY LEFT(title, 4) --or this-- ORDER BY SUBSTR(title, 1, 4) etc... Phil Thank you very much, that worked. > Something like this? > [quoted text clipped - 3 lines] > > Phil You are using ColdFusion functions where you need to be using SQL functions. What the exact function is will depend on which DBMS you use, but they are easy to find in the documentation and|or Google. I have been searching google and I keep coming up with trimming the white space off a result. lol. Back to the google. I am using MSSQL > I am using MSSQL I got this from "MSSQL string functions" it may help you in future projects. http://doc.ddart.net/mssql/sql70/fa-fz_23.htm http://doc.ddart.net/mssql/sql70/left.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.