2 Replies Latest reply on Dec 12, 2007 3:01 PM by The ScareCrow

    Help with SQL statement in a coldfusion application

    rere
      I created 2 drop down list boxes one that contains the months and the second that contains the year. Right now my sql statement is Select * from job where releaseDate=#imageDate#. Now this was written in 2006 and I didn't know they were going to continue to use the tool in 2007. So now I need a sql statement that if a user selects Jan 2007 that all of jan 2007 will display. The format for the datetime field in the database is Jan 2 2007 hh:mm:ss the first box is named imageDate and the second is called StatusDate
        • 1. Re: Help with SQL statement in a coldfusion application
          Level 7
          Take a look at the T-SQL datepart function.

          --
          Bryan Ashcraft (remove BRAIN to reply)
          Web Application Developer
          Wright Medical Technology, Inc.
          -------------------------------------------------------------------------
          Macromedia Certified Dreamweaver Developer
          Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/


          "rere" <webforumsuser@macromedia.com> wrote in message
          news:fjp4im$fqk$1@forums.macromedia.com...
          >I created 2 drop down list boxes one that contains the months and the
          >second
          > that contains the year. Right now my sql statement is Select * from job
          > where
          > releaseDate=#imageDate#. Now this was written in 2006 and I didn't know
          > they
          > were going to continue to use the tool in 2007. So now I need a sql
          > statement
          > that if a user selects Jan 2007 that all of jan 2007 will display. The
          > format
          > for the datetime field in the database is Jan 2 2007 hh:mm:ss the first
          > box is
          > named imageDate and the second is called StatusDate
          >


          • 2. Help with SQL statement in a coldfusion application
            The ScareCrow Level 1
            Try this, note the month and year functions are db dependant so you may need to find the ones for the db you are using. These will work for ms access and sql server

            Select *
            From job
            Where Month(releaseDate) = #imageDate#
            And Year(releaseDate) = #statusDate#

            Note: I would also suggest not using "*" but list the columns you will need

            Ken