Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- >
- ColdFusion
- >
- convert datetime to decimal

Highlighted

Community Beginner
,

Jun 05, 2015

Copy link to clipboard

Copied

Does anyone know of a way to convert a datetime (like 2015-06-10 07:30:00) to a decimal, similar to:

42165.450 where 42165 is days and 450 is minutes? If I try the following:

------------------------------------------------------------------

<cfset dtNow = "2015-06-10" />

<cfset dtOne = CreateDateTime(

Year( dtNow ),

Month( dtNow ),

Day( dtNow ),

07, <!--- 6 PM. --->

30, <!--- 30 Minutes. --->

00 <!--- 0 Seconds. --->

) />

<!--- Dump out result. --->

<cfdump var="#NumberFormat(dtone,'99999.99999')#" /><br>

------------------------------------------------------------------

I end up with 42165.31250

Thanks

Adobe Community Professional

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 (2015-06-16 at 12:00) and (2015-06-16 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 zero-date of your system as follows.

You know that the date (2015-06-16 at 12:00) is equivalent to (42170 days + 720 minutes) after the zero-date. Converting everything into minutes gives (42170 x 24 x 60 + 720) minutes = 60725520 minutes. You then have

<!--- Test date is 2015-06-16 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 1899-12-30 23:00, which is equivalent to createDatetime(1899,12,30,23,0,0). You can then use this zero-date 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>

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Community Beginner
,

Jun 05, 2015

Copy link to clipboard

Copied

Does anyone know of a way to convert a datetime (like 2015-06-10 07:30:00) to a decimal, similar to:

42165.450 where 42165 is days and 450 is minutes? If I try the following:

------------------------------------------------------------------

<cfset dtNow = "2015-06-10" />

<cfset dtOne = CreateDateTime(

Year( dtNow ),

Month( dtNow ),

Day( dtNow ),

07, <!--- 6 PM. --->

30, <!--- 30 Minutes. --->

00 <!--- 0 Seconds. --->

) />

<!--- Dump out result. --->

<cfdump var="#NumberFormat(dtone,'99999.99999')#" /><br>

------------------------------------------------------------------

I end up with 42165.31250

Thanks

Adobe Community Professional

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 (2015-06-16 at 12:00) and (2015-06-16 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 zero-date of your system as follows.

You know that the date (2015-06-16 at 12:00) is equivalent to (42170 days + 720 minutes) after the zero-date. Converting everything into minutes gives (42170 x 24 x 60 + 720) minutes = 60725520 minutes. You then have

<!--- Test date is 2015-06-16 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 1899-12-30 23:00, which is equivalent to createDatetime(1899,12,30,23,0,0). You can then use this zero-date 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>

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

wannab0133

Jun 05, 2015
0
/t5/coldfusion/convert-datetime-to-decimal/td-p/7182474
4
Replies
4

Adobe Community Professional
,

Jun 05, 2015

Copy link to clipboard

Copied

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>

Community Guidelines

Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

Reply

Loading...

BKBK

Jun 05, 2015
1
/t5/coldfusion/convert-datetime-to-decimal/m-p/7182475#M168482
Community Beginner
,

Jun 13, 2015

Copy link to clipboard

Copied

It turns out that the data I am using is storing days as integers(days from 12-30-1899), and times as integer(minutes from midnight i.e 0-1439). So I am concatenating like this: days.minutes But, I am having an issue comparing the foillowing:

42170.720(2015-06-16 at 12:00) vs 42170.1218(2015-06-16 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?

Community Guidelines

Reply

Loading...

wannab0133

Jun 13, 2015
0
/t5/coldfusion/convert-datetime-to-decimal/m-p/7182476#M168483
Adobe Community Professional
,

Jun 14, 2015

Copy link to clipboard

Copied

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 (2015-06-16 at 12:00) and (2015-06-16 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 zero-date of your system as follows.

You know that the date (2015-06-16 at 12:00) is equivalent to (42170 days + 720 minutes) after the zero-date. Converting everything into minutes gives (42170 x 24 x 60 + 720) minutes = 60725520 minutes. You then have

<!--- Test date is 2015-06-16 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 1899-12-30 23:00, which is equivalent to createDatetime(1899,12,30,23,0,0). You can then use this zero-date 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>

Community Guidelines

Reply

Loading...

BKBK

Jun 14, 2015
0
/t5/coldfusion/convert-datetime-to-decimal/m-p/7182477#M168484
wannab0133
LATEST

Community Beginner
,

Jun 14, 2015

Copy link to clipboard

Copied

Community Guidelines

Reply

Loading...

wannab0133

Jun 14, 2015
0
/t5/coldfusion/convert-datetime-to-decimal/m-p/7182478#M168485
Using the Community
Experience League
Terms of Use
Privacy Policy
Cookie preferences
AdChoices
Language:

- Deutsch
- English
- Español
- Français
- 日本語コミュニティ
- Português

Copyright © 2020 Adobe. All rights reserved.