How to add new year?

Question

I would like to increment school year every time after previous school year is over. For example my current code looks like this:

WHERE schooldt BETWEEN '07/01/2016' AND '06/30/2017'

So once school year is over 06/30/2017 I would like to set new start date and new end date automatically. I was thinking about using dateAdd() in cold fusion. Is there any other way to do this and what would be the most efficient?

Thanks in advance.

Answer 1

I would like to increment school year every time after previous school year is over

Then implement logic that changes the date values used in your query based on the current month. If the current month is earlier than July, then you know the current school year is still in progress. So deduct one year to calculate the start date. Otherwise, move to the next year.

    <cfset today = now()>
    <!--- If the current school year is still in progress --->
    <cfif month(today) lt 7>
        <cfset startDate = createDate( year(today) - 1, 7, 1)>
        <cfset endDate = createDate( year(today), 6, 30)>
    <!--- Otherwise, move to next year --->
    <cfelse>
        <cfset startDate = createDate( year(today), 7, 1)>
        <cfset endDate = createDate( year(today) + 1, 6, 30)>
    </cfif>

As far as querying, two things to keep in mind:

• Date strings are ambiguous. Always use date objects instead.

• Be careful with date comparisons and the BETWEEN operator. If the SchoolDt column contained both a date and time, the result may not be what you expected. A more flexible construct (that stills works even if a column contains both date and time) is:

  WHERE SchoolDt >= <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date">
  AND   SchoolDt < <cfqueryparam value="#dateAdd('d', 1, endDate)#" cfsqltype="cf_sql_date">
  

  If you are using new Query(), parameterize the sql string and use addParam instead:

  yourQuery.addParam( name="startDate"
                     , value="#startDate#"
                     , cfsqltype="cf_sql_date" );
  
  yourQuery.addParam( name="endDate"
                     , value="#endDate#"
                     , cfsqltype="cf_sql_date" );