4 Replies Latest reply on Jun 22, 2013 12:43 PM by weezerboy

    CFSET time to today at 9:00PM

    weezerboy Level 1

      I need to update my SQL database at the end of each day for certain records.

       

      The time needs to be 9:00 PM each day (dont worry about time differences)

       

      So for today  I need to insert '2013-06-21 21:00:00' into the db.

       

      So I wantd to set the time before I inserted it.

       

      How do I do that?

       

      <CFSET mytime = ?>

       

      How do I get 9:00 PM each day?

        • 1. Re: CFSET time to today at 9:00PM
          BKBK Adobe Community Professional & MVP

          You want the format, 'yyyy-mm-dd HH:MM:SS'. There are two ways to get it, depending on whether you are inserting the value into a datetime column or into a text column.

           

          If you wish to save the value as a datetime column, then you should assign it as a date object. You can do that by modifying the datatype of the column to the required format. Alternatively, you can include a formatting function in the insert SQL statement for your particular database. It is then sufficient to represent the time as

           

          <cfset mytime = now()>

           

          If, however, the column is to be a text field (for example, char or varchar), then you could do something like this

           

          <cfset mytime = dateFormat(now(), "yyyy-mm-dd") & " " & timeFormat(now(), "HH:MM:SS")>

          • 2. Re: CFSET time to today at 9:00PM
            weezerboy Level 1

            The data type is datetime.

             

            So I get that if I wnted to enter the time as of now I would <cfset mytime = now()> and insert it.

             

            But what if I want the time to be 9:00PM today  or 9:00PM tomorrow and insert it?

            • 3. Re: CFSET time to today at 9:00PM
              BKBK Adobe Community Professional & MVP

              <cfset y = year(now())>

              <cfset m = month(now())>

              <cfset d = day(now())>

               

              <cfset today_9PM = createDatetime(y,m,d,21,0,0)>

              <cfset yesterday_9PM = dateAdd("d",-1,today_9PM)>

              <cfset tomorrow_9PM = dateAdd("d",1,today_9PM)>

              • 4. Re: CFSET time to today at 9:00PM
                weezerboy Level 1

                Excellent. Thank you!