CFSET time to today at 9:00PM

[weezerboy]

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?

[BKBK]

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

[weezerboy]

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 · Accepted solution]

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

[weezerboy]

Excellent. Thank you!