Skip to main content
W
weezerboy
Inspiring
June 21, 2013
Answered

CFSET time to today at 9:00PM

  • June 21, 2013
  • 1 reply
  • 969 views

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?

    This topic has been closed for replies.
    Correct answer BKBK

    <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)>

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 22, 2013

    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")>

    W
    weezerboyAuthor
    Inspiring
    June 22, 2013

    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?

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    June 22, 2013

    <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)>

    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

    Explore now
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices