add midnigh time to a date

[kt03]

Hi,

<cfset endDate = DateAdd("d", 1, endDate)> this will add one date into the endDate.  Instead of add one date, i want it to add the midnigh time to the endDate. such as: 2012/10/01 23:59:59

i had tried this

<cfset endDate= DateAdd("ms",86399997,endDate) /> but got errror below

ms is not a valid date/time format.

anyone can help me with this?

Thanks

[Dan_Bracuk]

The createdatetime function might be appropriate way to go.

[Steve Sommers · Accepted solution]

I'm assuming you want to use this adjusted date/time for some sort of "within" or "before" check in a query where clause. If so I usually add a day and combine it with a less than check. Using your endDate code:

<cfset variables.endDate = DateAdd("d", 1, variables.endDate) />

...

where date < <cfqueryparam value="#variables.endDate#" cfsqltype="CF_SQL_TIMESTAMP" />

...

Another option, assuming endDate is truncated to just the date (no hours, minutes, or seconds), you can use createTime to add the time:

<cfset variables.endDate = variables.endDate + createTime(23,59,59) />

Another option is to add a day and substract a second.

[kt03]

worked, thanks alot