tracker issue : CF-4202965

select a category, or use search below
(searches all categories and all time range)

DateConvert Function seems corrupt.

| View in Tracker

Status/Resolution/Reason: To Track//PRNeedInfo

Reporter/Name(from Bugbase): Michael Grant / ()

Created: 06/21/2018

Components: Tags

Versions: 11.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: 11,0,14,307976 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Windows 10 64 bit

Vote Count: 3

Problem Description:
Using DateConvert() inside DateAdd() creates a corrupt date/time object.
Please refer to this conversation

Steps to Reproduce:
Please refer to this conversation

Actual Result:
Evaluating two time objects gives unexpected results. Time 2 evaluates as earlier than Time 1

Expected Result:
Time 1 is later than Time 2.

Any Workarounds:
Wrapped DateAdd() function in ParseDateTime() to get a proper date/time object.



Correction: Under "actual result" it's actually Time 1 that evaluates as earlier than Time 2. In my test time1 was: {ts '2018-06-20 15:12:00'} and time2 was {ts '2018-06-20 09:00:00'} yet time1 evaluated as false when I did a GTE than time2. Sorry for the confusion.
Comment by Michael G.
29121 | June 21, 2018 04:40:36 PM GMT
I can confirm this problem exists for CF 11,0,14,307976 JAVA 1.8.0_151
Vote by Eddie L.
29122 | June 21, 2018 04:41:20 PM GMT
@Michael We could see that you have used getDate() in the code, if you are setting anything specific to time zone or locale in this function please share with us, it would be helpful for us to repro. We tried the code below, we have used now() instead of getDate(). It worked as expected. <!--- Set date ---> <cfset thisDate = now()> <!--- Determine what flight they should see. ---> <!--- If it's after 9am and before 10pm ---> <cfif thisDate GTE CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),9, 0,0) AND thisDate LT CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),21 ,0,0)> <cfset d1 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 9:00:00"> <cfset d2 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 21:00:00"> <cfoutput>If it's after 9am and before 10pm</cfoutput> <!--- If it's after 10pm ---> <cfelseif thisDate GTE CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),21 ,0,0)> <cfset d1 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 21:00:00"> <cfset d2 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 9:00:00"> <cfset d2 = DateAdd("d",1,d2)> <cfoutput>If it's after 10pm</cfoutput> <!--- If it's before 9am ---> <cfelse> <cfset d1 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 21:00:00"> <cfset d1 = DateAdd("d",-1,d1)> <cfset d2 = "#DatePart('m',thisDate)#/#DatePart('d',thisDate)#/#DatePart('yyyy',thisDate)# 9:00:00"> <cfoutput>If it's before 9am</cfoutput> </cfif>  
Comment by Dattanand M.
29251 | July 06, 2018 09:25:52 AM GMT
@Dattanand, you won't see the problem when setting "thisDate" to now(). As the OP points out, the issue surfaces when using the DateConvert() function inside of the DateAdd() function. Please see the referenced thread for reproducible examples.
Comment by Eddie L.
29447 | August 08, 2018 08:14:37 PM GMT
@Michael, In the getDate() function you have used application.GMTOffset, could you please let us know what value you have  set to this variable ? Also let us know the time zone set on the machine.
Comment by Dattanand M.
29713 | September 18, 2018 07:32:18 AM GMT
@Dattanand Application.GMTOffset is "5" to reflect EDT. The timezone on the machine is Pacific (utcHourOffset = 7).
Comment by Michael G.
29717 | September 18, 2018 03:21:10 PM GMT
@Michael  thanks for the info, we will check from our end.
Comment by Dattanand M.
29741 | September 27, 2018 06:24:07 AM GMT
Hi Michael, We tried with the code you had shared in the forum, only difference is I have assigned date1 =CreateODBCDateTime(CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),15,12,0) just to repro with the exact time you had specified. I am getting the output as below.   Please let me know if we are missing out something here.   application.GMTOffset set to 5 Code: <cfscript> function getDate() { return DateAdd('h',(application.GMTOffset*-1),DateConvert("local2utc", Now())); } </cfscript> <cfset thisDate = getDate()> <cfset date1 = CreateODBCDateTime(CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),15,12,0))> <cfset date2 = CreateODBCDateTime(CreateDateTime(DatePart("yyyy",thisDate),DatePart("m",thisDate),DatePart("d",thisDate),9,0,0))> <cfoutput> date1: #date1#<br /> date2: #date2#<br /> <br /> DateCompare: #DateCompare(date1,date2)#<br /> GTE Evaluation: #date1 GTE date2#<br /> </cfoutput>   Output: date1: \{ts '2018-09-28 15:12:00'} date2: \{ts '2018-09-28 09:00:00'} DateCompare: 1 GTE Evaluation: YES
Comment by Dattanand M.
29747 | September 28, 2018 09:07:47 AM GMT
MIchael, if you could check and confirm, that would be great.
Comment by HariKrishna K.
29758 | October 05, 2018 05:08:18 AM GMT
Hi, sorry it took a while to respond. I don't have a lot of free time at the moment. I can confirm that the code you posted evaluates the same as your output. However I fail to see the value in this since what you've posted is different than what I posted. When you run what I posted, like @Eddie Lotter did, do you get the same result as myself and Eddie both did? Cheers, Michael
Comment by Michael G.
29779 | October 10, 2018 02:26:45 PM GMT
The problem still exists. I will post my very simple example in the comments, as my use case for dateConvert() was different, but the same parseDateTime() workaround fixed the problem. Running CF2016, Update10 (2016.0.10.314028) Java 11.0.2
Vote by Chris J.
30502 | March 14, 2019 12:26:46 AM GMT
The following example also produces the dateConvert() bug: <cfscript> variables.localTime = createODBCDateTime("{ts '2019-03-13 17:48:00'}"); variables.currentUtc = dateConvert('local2Utc', variables.localTime); variables.targetUtc = createODBCDateTime('3/13/2019 10:48:00 PM'); variables.difference = dateDiff('s', variables.currentUtc, variables.targetUtc); writeOutput('LOC TIME: ' & variables.localTime & '<br />'); writeOutput('CUR TIME: ' & variables.currentUtc & '<br />'); writeOutput('NEW TIME: ' & variables.targetUtc & '<br />'); writeOutput('DIF TIME: ' & variables.difference & '<br />'); </cfscript> I can also confirm that the parseDateTime() fix works here too. So, updating line 2 to the following fixes the problem: variables.currentUtc = parseDateTime(dateConvert('local2Utc', variables.localTime)); I realize this is probably really low priority given that there's a workaround, but it would be nice to see this fixed. Cheers, Chris
Comment by Chris J.
30501 | March 14, 2019 12:36:47 AM GMT