issues the dates as they are being used as mathematical calculations
I have a CFM Code where the date calculation has been done using a mathematical operator and this allways end up in some kind of weird date when the month has 31 days and it calculates the next date as next month having again 31days like Nov31st
The logic seems weird as it does not take into consideration the leap year and anything else
Please guide me with date Functions or at least help me in that date functions so i can get the value properly instead of ending with wrong data format
Here is the code snippet
<cfset rsQuery = getDataFromQuery()>
<cfif len(left(rsQuery.EndDate,2)) AND len(left(rsQuery.StartDate,2))>
<cfif left(rsQuery.EndDate,2) EQ 12>
<cfset cxStartMonth = 1>
<cfelse>
<cfset cxStartMonth = left(rsQuery.EndDate,2) + 1>
</cfif>
<cfif left(rsQuery.StartDate,2) eq 01>
<cfset cxEndMonth = 12>
<cfelse>
<cfset cxEndMonth = left(rsQuery.StartDate,2) - 1>
</cfif>
<cfif nextQuery.UserID EQ 1>
<cfset EndMonthstring = monthAsString(left(rsQuery.EndDate,2))>
<cfset StartMonthstring = monthAsString(left(rsQuery.StartDate,2))>
<cfset cxStartMonthFinal = monthAsString(cxStartMonth)>
<cfset cxEndMonthFinal = monthAsString(cxEndMonth)>
<cfelse>
<cfset EndMonthF = left(rsQuery.EndDate,2)>
<cfset EndMonthFD = createDate(year(Now()), EndMonthF, day(now()))>
<cfset EndMonthstring = LSDateFormat(EndMonthFD,"mmmm","fr")>
<cfset StartMonthF = left(rsQuery.StartDate,2)>
<cfset StartMonthFD = createDate(year(Now()), StartMonthF, day(now()))>
<cfset StartMonthstring = LSDateFormat(StartMonthFD,"mmmm","fr")>
<cfset cxStartMonthFinalFD = createDate(year(Now()),cxStartMonth, day(now()))>
<cfset cxStartMonthFinal = LSDateFormat(cxStartMonthFinalFD,"mmmm","fr")>
<cfset cxEndMonthFinalFD = createDate(year(Now()),cxEndMonth, day(now()))>
<cfset cxEndMonthFinal = LSDateFormat(cxEndMonthFinalFD,"mmmm","fr")>
</cfif>
<cfset StartDatevalue = right(rsQuery.StartDate,2)>
<cfset EndDatevalue = right(rsQuery.EndDate,2)>
<cfset getYear = year(Now())>
<cfset Monthfomatstart = rsQuery.StartDate & "/" & getYear>
<cfset MonthfomatEnd = rsQuery.EndDate & "/" & getYear>
<cfset DateFormatstart = DateFormat(DateAdd('d', -1, Monthfomatstart),'mmmm dd, yyyy')>
<cfif len(day(DateFormatstart)) EQ 1>
<cfset cxEndDate = "0" & day(DateFormatstart)>
<cfelse>
<cfset cxEndDate = day(DateFormatstart)>
</cfif>
<cfset DateFormatEnd = DateFormat(DateAdd('d', 1, MonthfomatEnd),'mmmm dd, yyyy')>
<cfif len(day(DateFormatEnd)) EQ 1>
<cfset cxStartDate = "0" & day(DateFormatEnd)>
<cfelse>
<cfset cxStartDate = day(DateFormatEnd)>
</cfif>
<cfset Period = left(rsQuery.EndDate,2)-left(rsQuery.StartDate,2)>
<cfset PeriodMonth = val(Period) + 1>
</cfif>The Dates are stored in the query as:
StartDate EndDate
05/01 10/31
ASKER
and that is what I asked
Could you explain what the code is supposed to be doing in plain English? Specifically, what is it doing that can't be achieved with regular CF date functions.
> The Dates are stored in the query as:
What's the column data type of "StartDate" and "EndDate" in your database: varchar OR datetime?
> allways end up in some kind of weird date
There are a lot of variables in the code.. so which one of them is wrong and what result were you expecting instead?
> The Dates are stored in the query as:
What's the column data type of "StartDate" and "EndDate" in your database: varchar OR datetime?
> allways end up in some kind of weird date
There are a lot of variables in the code.. so which one of them is wrong and what result were you expecting instead?
ASKER
it's a varchar field in database for start and end dates
ASKER
please see the lines from 3 to 12 (using matematical operator to add a month and wehatever)
now it reaches a point where it is using a createdate to create a date for the values passed year/month/day
specially error is happening on this line
where it enters into sometimes invalid based upon the date it gets from database column,
i have seen the date ending as: 2021/11/31 - which is wrong, you cannot have this date, and same cases i am also thinking of it can happen during a leap year or month having 28 days
so the whole calculation of this piece of code is ending in wrong direction so i need to remove that and come up with a solution which cannot end in an error
and for dates, i have no clue
now it reaches a point where it is using a createdate to create a date for the values passed year/month/day
specially error is happening on this line
<cfset cxStartMonthFinalFD = createDate(year(Now()),cxStartMonth, day(now()))>where it enters into sometimes invalid based upon the date it gets from database column,
i have seen the date ending as: 2021/11/31 - which is wrong, you cannot have this date, and same cases i am also thinking of it can happen during a leap year or month having 28 days
so the whole calculation of this piece of code is ending in wrong direction so i need to remove that and come up with a solution which cannot end in an error
and for dates, i have no clue
> please see the lines from 3 to 12 (using matematical operator to add a month and wehatever)
Yes, I understand how the functions work and why you're getting unexpected results. But I don't know why you need all that code just to add a month to a date. Instead, create proper date objects by appending the desired year, and then .. add a month. If it's something more than that, you'll have to elaborate so we can suggest an alternative.
<!--- Convert string "05/01" and "10/31" into date objects --->
<cfset currentYear = year(Now())>
<cfset dtStartDate = parseDateTime( rsQuery.StartDate &"/"& currentYear, "MM/dd/yyyy" )>
<cfset dtEndDate = parseDateTime( rsQuery.EndDate &"/"& currentYear,"MM/dd/yyyy" )>
<!--- Add 1 month to both --->
<cfset nextStartDate = dateAdd("m", 1, dtStartDate)>
<cfset nextEndDate = dateAdd("m", 1, dtEndDate)>
Result:
(String) StartDate/EndDate = 05/01 - 10/31
(Date) StartDate/EndDate = {ts '2021-05-01 00:00:00'} - {ts '2021-10-31 00:00:00'}
(Date) Next StartDate/EndDate = {ts '2021-06-01 00:00:00'} - {ts '2021-11-30 00:00:00'}
Yes, I understand how the functions work and why you're getting unexpected results. But I don't know why you need all that code just to add a month to a date. Instead, create proper date objects by appending the desired year, and then .. add a month. If it's something more than that, you'll have to elaborate so we can suggest an alternative.
<!--- Convert string "05/01" and "10/31" into date objects --->
<cfset currentYear = year(Now())>
<cfset dtStartDate = parseDateTime( rsQuery.StartDate &"/"& currentYear, "MM/dd/yyyy" )>
<cfset dtEndDate = parseDateTime( rsQuery.EndDate &"/"& currentYear,"MM/dd/yyyy" )>
<!--- Add 1 month to both --->
<cfset nextStartDate = dateAdd("m", 1, dtStartDate)>
<cfset nextEndDate = dateAdd("m", 1, dtEndDate)>
Result:
(String) StartDate/EndDate = 05/01 - 10/31
(Date) StartDate/EndDate = {ts '2021-05-01 00:00:00'} - {ts '2021-10-31 00:00:00'}
(Date) Next StartDate/EndDate = {ts '2021-06-01 00:00:00'} - {ts '2021-11-30 00:00:00'}
ASKER
honestly I do not have full context what their whole code is doing it this way
all I was trying to do is to simplify the code to reproduce the same code as to what the existing code is doing
all I was trying to do is to simplify the code to reproduce the same code as to what the existing code is doing
Update:
Well you can't reproduce something without knowing the expected result (-; Frankly, I don't understand the "logic"' either. i.e. I can see its calculating various date parts, and creating a bunch of variables, but I don't why it's doing that. (Are all of those variables even used elsewhere?) Tbh, it could just be a convoluted way of "adding a month to a date". Hard to say without more context ...
Well you can't reproduce something without knowing the expected result (-; Frankly, I don't understand the "logic"' either. i.e. I can see its calculating various date parts, and creating a bunch of variables, but I don't why it's doing that. (Are all of those variables even used elsewhere?) Tbh, it could just be a convoluted way of "adding a month to a date". Hard to say without more context ...
ASKER
they are also using locale in the code
ASKER
i am in the same boat why so many variables and that is an issue
but breaking line by line can we fix one issue which is happening now which is:
see this code
https://trycf.com/gist/6a304e648e8311b280feccd3b308f0ef/lucee5?theme=monokai
but breaking line by line can we fix one issue which is happening now which is:
<cfset cxStartMonthFinalFD = createDate(year(Now()),cxStartMonth, day(now()))>see this code
https://trycf.com/gist/6a304e648e8311b280feccd3b308f0ef/lucee5?theme=monokai
ASKER
i am tried replicating the code i have in my db and how the calculations are performed
we just need to remove the +1 and -1 and use the date functions to make sure i ended in valid date
we just need to remove the +1 and -1 and use the date functions to make sure i ended in valid date
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, didn't see all those posts. Let me read back...
ASKER
and yes most of the variables are used in the code, as part of included files
> we just need to remove the +1 and -1 and use the date functions to make sure i ended in valid date
Not just that, but this line of code is never going to work in ALL months as you already discovered
So is this someon'e code you're trying to fix? If yes, trying searching the code to identify which of many the variables created are actually being used? Then you can rewrite the code to use proper date functions, and avoid the various problems that come with this kind of home spun date logic.
Not just that, but this line of code is never going to work in ALL months as you already discovered
<cfset cxStartMonthFinalFD = createDate(year(Now()),cxStartMonth, day(now()))>So is this someon'e code you're trying to fix? If yes, trying searching the code to identify which of many the variables created are actually being used? Then you can rewrite the code to use proper date functions, and avoid the various problems that come with this kind of home spun date logic.
ASKER
biggest issue seems to here
and this part (below part seems ok as it will not end up in negative as i can see here)
<cfif left(rsQuery.EndDate,2) EQ 12>
<cfset cxStartMonth = 1>
<cfelse>
<cfset cxStartMonth = left(rsQuery.EndDate,2) + 1>
</cfif>
<cfif left(rsQuery.StartDate,2) eq 01>
<cfset cxEndMonth = 12>
<cfelse>
<cfset cxEndMonth = left(rsQuery.StartDate,2) - 1>
</cfif>and this part (below part seems ok as it will not end up in negative as i can see here)
<cfif len(day(DateFormatEnd)) EQ 1>
<cfset cxStartDate = "0" & day(DateFormatEnd)>
<cfelse>
<cfset cxStartDate = day(DateFormatEnd)>
</cfif>
<cfset Period = left(rsQuery.EndDate,2)-left(rsQuery.StartDate,2)>
<cfset PeriodMonth = val(Period) + 1>
Our posts crossed again...
ASKER
and whatever you mentioned is the only place where this whole calculation is happening, can you guide through that using a date field to make sure we always end up in better date and not invalid date
we can use some function which will handle the dates properly so it ends up in valid date instead of just outputting just like 2021/11/31 which is not valid
need to consider cases for month of fed and leap year too
we can use some function which will handle the dates properly so it ends up in valid date instead of just outputting just like 2021/11/31 which is not valid
need to consider cases for month of fed and leap year too
ASKER
i wrote this function but it does not seems entirely right
function calculateDate_Month(data) {
if(day(now()) eq 1) {
var months = '2,4,6,9,11';
var leapYear = IsLeapYear(now());
var _sDate = '';
if(leapYear && data == 2) {
_sData = 2;
}else if(data == 2) {
_sData = 3;
} else {
_sData = 1;
}
if(listcontains(months,'#data#')) {
var retData = day(1) - _sData;
}
} else {
var retData = data;
}
return retData;
}
> i wrote this function but it does not seems entirely right
Never roll your own date logic. Using home spun logic, instead of core date functions, is what caused the original problem.
> and yes most of the variables are used in the code, as part of included files
It seems unlikely ALL of those variables are actually being used beyond that code block
> can you guide through that using a date field to make sure we always end up in better date and not invalid date
Not without an explanation of what results are expected - and why. Sure you could force the values into something that's a valid date, but with ZERO information about expected results, it's anyone's guess as to whether the result would be correct ...
Maybe someone else will have more luck deciphering it.
Never roll your own date logic. Using home spun logic, instead of core date functions, is what caused the original problem.
> and yes most of the variables are used in the code, as part of included files
It seems unlikely ALL of those variables are actually being used beyond that code block
> can you guide through that using a date field to make sure we always end up in better date and not invalid date
Not without an explanation of what results are expected - and why. Sure you could force the values into something that's a valid date, but with ZERO information about expected results, it's anyone's guess as to whether the result would be correct ...
Maybe someone else will have more luck deciphering it.
ASKER
well I do not what else I can explain
all I need is that code block should render correct date block but not sure what other outwardly context is needed here
if that error cannot be fixed by an expert then what is context of this whole question
all I need is that code block should render correct date block but not sure what other outwardly context is needed here
if that error cannot be fixed by an expert then what is context of this whole question
in the enum you showed which are fields in the database and which are calculated fields? If a field is calculated why is it in the database?
all date fields should not be varchars but datetime it is not only more efficient (storagewise) but easier to manipulate the datestart/dateend should also have a year stored in the database (it doesn't have to be shown) (again a datetime object makes this so much easier)
how are you trying to calculate the date end? is it a period i.e. 6 months or 180 days? greater than the start date?
all date fields should not be varchars but datetime it is not only more efficient (storagewise) but easier to manipulate the datestart/dateend should also have a year stored in the database (it doesn't have to be shown) (again a datetime object makes this so much easier)
how are you trying to calculate the date end? is it a period i.e. 6 months or 180 days? greater than the start date?
Not sure , while working with date(s), why you are not using ColdFusion Functions : (Date and time functions (adobe.com) )
These take care of handling different calculations, including date diff, dateadd, etc.
Just convert the startDate and endDate using CreateDate Function. Build in ColdFusion functions should be used, in lieu of home grown date calculations functions.
These take care of handling different calculations, including date diff, dateadd, etc.
Just convert the startDate and endDate using CreateDate Function. Build in ColdFusion functions should be used, in lieu of home grown date calculations functions.
ASKER
if you can rewrite my logic and explain it to me
much much appreciated
I do not want to end up wrong dates
much much appreciated
I do not want to end up wrong dates
ASKER
giving advice is one thing but actually helping with code is another thing, advised by other experts too, repeating same sentence will not help. if anyone can guide in fixing the issue, please guide else there is no reason to put suggestions on using date /time, i know i have to use date/time but what could be the potential rewrite, I need help there
Where does Period come from?
<cfset rsQuery = getDataFromQuery()>
<cfset monthStart = left(rsQuery.StartDate,2)>
<cfset dayStart = right(rsQuery.StartDate,2)>
cfset year = DatePart("yyyy",Now()>
<cfset DStartDate = createDate(year,monthstart,daystart)>
<cfset PeriodMonthDateAdd("m" Period,DStartDate>
I would start by getting rid of string dates and use date dates. this simplifies the coding considerably.