
1. Re: convert datetime to decimal
BKBK Jun 5, 2015 3:08 PM (in response to wannab0133)There is actually no such thing as a conversion of a date to a number of days. Methods like #NumberFormat(dtone,'99999.99999')# or #dtone+0# are unofficial.
The proper way to do it is to convert a time period into days. So you first have to define your 'zero' date.
In Coldfusion, the zero date is 12 A.M., December 30, 1899. But it is usually, rather confusingly, given the value createdatetime(1899,12,29,12,0,0). Hence.
<cfset cfZeroDate = createdatetime(1899,12,29,12,0,0)>
<cfset myTestDate = createdatetime(2015,06,10,07,30,0)>
<! Number of days = (number of hours in time period)/24 >
<cfoutput>#datediff("h",cfZeroDate,myTestDate)/24#</cfoutput><br>

2. Re: convert datetime to decimal
wannab0133 Jun 13, 2015 11:49 AM (in response to BKBK)It turns out that the data I am using is storing days as integers(days from 12301899), and times as integer(minutes from midnight i.e 01439). So I am concatenating like this: days.minutes But, I am having an issue comparing the foillowing:
42170.720(20150616 at 12:00) vs 42170.1218(20150616 at 20:18)
It is telling me the first date at noon is GT the other date. So, I need to pad the times with zeros, so it becomes 42170.0720 instead of 42170.720. My qoq is here:
<cfquery name="dutyEndQuery" dbtype="query">
SELECT * FROM refData
WHERE EMPLOYEEID = '#passedEmployeeID#'
AND CAST( CAST(ACTIVITYDATE as VARCHAR) + '.' + CAST(ACTIVITYTIME as VARCHAR) AS DOUBLE ) >= #passedDutyDateTime#
</cfquery>
Is there anyway in a qoq to pad the time portion with zeros and make the total size 4 in order to change 720 to 0720?

3. Re: convert datetime to decimal
BKBK Jun 14, 2015 4:05 AM (in response to wannab0133)Padding with zero is not the answer. What you are actually writing as .720 and .1218 are, in fact, not decimals.
As you yourself have suggested, the values 720 and 1218 stand for number of minutes. Thus, the time, in minutes, corresponding to the dates (20150616 at 12:00) and (20150616 at 20:18) are, respectively, 12x60=720 and 20x60+18=1218. You can solve the problem in 2 ways:
1)
Interpret the values 42170 / 720 and 42170 / 1218, respectively, as 42170+720/1439=42170.500 and 42170+1218/1439=42170.846
2)
Take a step back to my last post. You can determine the zerodate of your system as follows.
You know that the date (20150616 at 12:00) is equivalent to (42170 days + 720 minutes) after the zerodate. Converting everything into minutes gives (42170 x 24 x 60 + 720) minutes = 60725520 minutes. You then have
<! Test date is 20150616 at 12:00 >
<cfset myTestDate = createdatetime(2015,06,16,12,0,0)>
<! Zero date is 60725520 minutes prior to test date >
Zero date: <cfoutput>#dateAdd("n",60725520,myTestDate)#</cfoutput>
This tells you that your zero date is 18991230 23:00, which is equivalent to createDatetime(1899,12,30,23,0,0). You can then use this zerodate as the basis for all further date calculations.
Suppose you wish to find any date corresponding to the format number_of_days / number_of_minutes, for example, 42170 / 1218. All you now have to do is convert everything into minutes and add to the zero date. Thus,
<cfset zeroDate = createdatetime(1899,12,30,23,0,0)>
<cfset newDate = dateAdd("n",42170*24*60+1218,zeroDate)>
<cfoutput>#newDate#</cfoutput>

4. Re: convert datetime to decimal
wannab0133 Jun 14, 2015 8:19 AM (in response to BKBK)I agree that option 2 is the best option, but I need to make these calculations within SQL qoq's, where I cannot use dateadd or datediff, rather than manipulating this information within a coldfusion component. So, I am going to utilize option 1. Thank you for your help with this.