Working with Dates

Dates and Timestamps

There are two data types for working with dates and time:

• Date – This is measured in milliseconds from 1/1/1970.  Captures the date.  If a Date field is not initialized – not set to something – it will have the value 1/1/1970   (that is, a zero or unset date is January 1, 1970).   Most “date arithmetic” is done with Date values.
• Timestamp – This captures both “date” and “time”.

Date & Time Data Types

There are two data types relating to dates:

• “Date” Data Types
  1. Date – Date/Time measured in milliseconds from 1/1/1970 00:00
  2. Timestamp – Date/Time measured in microseconds from 1/1/1970 00:00

Roughly, “Date” fields hold date information – and may or may not include time.  “Timestamps” contain both date and time information.   The "zero" date - an unset or 'empty' date - will appear as 1/1/1970.

These are not interchangeable:  you cannot compare a Date field to a Timestamp field.  You can convert a Timestamp to a Date using the “toDate” function (see below).  You can cannot convert Dates (or anything else) to a Timestamp – there is no “toTimestamp” function.

All ‘date arithmetic’ is done using the Date data type.

Making Dates

You can convert a String to a Date using the rw:toDate function.  The String has to be in the form “m/d/yyyy” (or mm/dd/yyyy).   E.g. rw:toDate(‘12/21/2024’)

Getting Dates from Users (The Date Parameter Type in REportWriter)

The Date parameter type presents the user with a Calendar popup and returns a parameter with a Date data type.  This can be used, as is, as a Date – it does not need to be converted.

The Zero or “Empty” Date

Dates – and Timestamps – that have no value, that is, they are “empty”, will print out as January 1, 1970.   This is the “zero date”.  If you see a date printed as 1/1/1970 it is because it has not been set to any actual date.   (It would be quite rare for 1/1/1970 to be “real” – perhaps as a staff member’s birth date). The zero Timestamp is 1/1/1970 00:00am.

Time (e.g. 12:45pm) can be represented either as a Timestamp or a Date with 'empty' or zero month, day and year fields.  A Date with time but no date will print out like this example:   1/1/1970 12:45pm     To suppress the 'empty' month, day and year fields, use the 'rw:formatDate' function to only print the time fields - see below.

Printing Dates & Timestamps

The formatDate function is used to format a Date or a Timestamp field for output.  This is required to make sure the Date or Timestamp prints the way you want it to.

                             rw:formatDate(Date dateToPrint, String outputFormat)

Some Relevant Format Characters

The most usual date/time format characters include:

Some other interesting ones include:

There are many more format characters.  To find the full repertoire of date format characters, go here and scroll down:   https://unicode-org.github.io/icu/userguide/format_parse/datetime/
 

Comparing Dates

One of the most common things to do is to compare dates to determine which is earlier, which is later.   The dateCompare function is used for this and IT DOES NOT RETURN A BOOLEAN VALUE:  it returns an integer value.

                             rw:dateCompare(Date startDate, Date compareDate)

The first argument to the function “dateCompare” is the starting date, the date against which the second date will be compared.

• Dates are Equal:  If the compareDate is the same date as the startDate, dateCompare returns 0 (zero).
• compareDate is earlier than the startDate:  dateCompare returns < 0  (a random value less than zero).   Note that the definition says it will return -1:  this is not always true.  Test for <0.
• compareDate is after/later than the startDate: dateCompare returns > 0 (a random value > 0.

How to use dateCompare

Use two columns to do this to reduce the computing load:

• Put the actual compare in one column:  rw:dateCompare(row.startingDate, param.compareDate)
• In a second column, use the result of the first column to decide what to do, using the “nested Conditional statements” technique:

${row.theResult == 0 ? ‘Dates are Equal’ :  row.theResult < 0 : ‘Date is EARLIER’ : ‘Date is LATER’}

            Or

${row.theResult == 0 ? ‘Dates are Equal’ :  row.theResult < 0 : ‘Date is EARLIER’ : row.theResult > 0 ? ‘Date is LATER’ : ‘uh oh. Something is wrong’}

Always test the result of dateCompare using <0 and >0 and not ‘== -1’ or ‘== 1’.

Date Arithmetic

Adding or Subtracting “Days” from a Start Date

The “dateAddDays” function can be used to calculate a new Date either before or after a start date.

Format:  rw:dateAddDays(Date startDate, int offsetNumberOfDays)               

This will add the integer value “offsetNumberOfDays” to the “startDate” field, which must be a Date.

Finding the Number of Days or School Days between Two Dates

There are two functions that can be used to calculate the number of days between two dates:

• getNumberOfDays – This returns the number of days between two dates, inclusive of the two dates.  Both values must have the “Date” data type:  

rw:getNumberOfDays(Date date1, Date date2)

• getNumberOfSchoolDays – This returns the number school days between two dates, inclusive, using the school’s calendar.   Both values must have the “Date” data type:  

rw:getNumberOfSchoolDays(Date date1, Date date2)

All Date Functions:
String convertMinutesToFriendly(Integer minutes)
 

Return as a string the number of minutes and hours based on mintes. 61 would return 1 hour 1 minute.
 Usage: ${rw:convertMinutesToFriendly(minutes)}

Date dateAddDays(Date startingValue, int offset)
 

Adds int1 days to Date1
 Usage: ${rw:dateAddDays(startingValue, offset)}

Integer dateCompare(Date startingDate, Date compareDate)
 

Returns an integer representing the comparison of two dates. 0 if they are the same, -1 if Date1 < Date2 and 1 if Date1 > Date2
 Usage: ${rw:dateCompare(startingDate, compareDate)}

Date dateFindDayOfWeek(Date startingValue, String dayToFind, int weekOffset)
 

Return a date that is the next or previous day in the week by name. String1 is the day of the week to find (Monday, Tuesday etc...). int1 is 0 for this week, -1 for last week etc.
 Usage: ${rw:dateFindDayOfWeek(startingValue, dayToFind, weekOffset)}

String formatDate(Date value, String pattern)
 

Returns a formatted string for Date or Timestamp types using the pattern (e.g. 'MM/dd/yyyy').
 Usage: ${rw:formatDate(value, pattern)}

int getAgeAsOfDate(Date asOfDate, Date birthDate)
 

Determine someone's age as of date. 1/1/2017, 1/1/2022 would return 5
 Usage: ${rw:getAgeAsOfDate(asOfDate, birthDate)}

int getDayIndex(String dayToFind)
 

Return a number representing the name of a day of the week. It looks at just the first 2 characters to decide. Su=1, Mo=2, Tu=3, We=4, Th=5, Fr=6, Sa=7
 Usage: ${rw:getDayIndex(dayToFind)}

int getDayOfYear(Date date)
 

Return a number that is the day of the year. 1/1/2018 would return 1. 2/1/2018 would return 32.
 Usage: ${rw:getDayOfYear(date)}

int getNumberOfDays(Date date1, Date date2)
 

Return the total number of days between dates. 1/1/2018, 1/2/2018 would return 2
 Usage: ${rw:getNumberOfDays(date1, date2)}

int getNumberOfSchoolDays(Date date1, Date date2)
 

Return the total number of school days between dates.
 Usage: ${rw:getNumberOfSchoolDays(date1, date2)}

Date toDate(String value)
 

Convert the date represented in String1 to a real date. Dates should be in the pattern of m/d/yyyy
 Usage: ${rw:toDate(value)}