2 Replies Latest reply on Jan 3, 2007 5:52 PM by Dan Bracuk


    TESS_GEAR Level 1
      I set datebilled field as a datetime data type in MS SQL 2005 server and the form value of this field is "mmyy" (for example, 0107) instead of "mm-dd-yyyy".

      Data in the database

      I need to list all records which contains January 2007, so the output will be 01-02-2007, 01-12-2007, 01-15-2007.

      anyone can help me to wirte a query on this case? I cannot think of manuplating the form variable in order to get the list.
      <cfset form.dtbilled = "0107">
      select dtbilled from aprtment where dtbilled = '#form.dtbilled#' : if i use this query, it will bring up nothing.
        • 1. Re: datetime
          dempster Level 1
          You can use ColdFusion string and date functions to create date values from your form input.

          The left 2 characters give you the month, and the right two plus 2000 give you the year. So your start date is this:

          <CFSET startdate = CreateDate(2000 + Right(Form.tdate,2), Left(Form.tdate, 2), 1)>

          If you add a month to your start date, you get your end date:

          <CFSET enddate = DateAdd("m", 1, startdate)>

          With these values, you can query the date values in your table.
          • 2. Re: datetime
            Dan Bracuk Level 5
            Fortunately for you, you are storing dates as dates so the mssql format is irrelevent. All date functions in cf and your db will work with your data.

            Dempster got you started. The rest of it is:

            select somestuff
            from sometables
            where somedatefield >= <cfqueryparam cfsqltype="cf_sql_date" value = "#startdate#">
            and somedatefield < <cfqueryparam cfsqltype="cf_sql_date" value = "#enddate#">