Page Comparison

Panel

name	blue

Useful methods frequently used in the system concerning date, SQL and metadata handling.

What does this section cover?

Table of Contents

...

Expand

title	Truncate Date

truncate(Date date) method returns the date rounded to the day with a time of 00:00:00:00. setEndOfDay(Date date) method returns the date rounded to the day with a time of 23:59:59:999.

Code Block

title	Example

public DateRange getDateRange(Date fromDate, Date toDate){
	if (fromDate!=null && toDate!=null )
	{
		fromDate = DateUtil.truncate(fromDate);
		toDate = DateUtil.setEndOfDay(toDate);
		range = new DateRange(fromDate, toDate);
	}
		
	return range;
}

...

Expand

title	Retrieve current date

1. getCurrentDate(String timeZoneID) method calculates and returns the current date based on the given time zone ID (the ID for a TimeZone, either an abbreviation such as "PST", a full name such as "America/Los_Angeles", or a custom ID such as "GMT-8:00". Note that the support of abbreviations is for JDK 1.1.x compatibility only and full names should be used).

Code Block

title	Example

...	
	Date currentDate = DateUtil.getCurrentDate(getCRMSession().getTimezone());
		
...

OR

2. getCurrentDate(TimeZone timeZone) method calculates and returns the current date based on the given time zone object (java.util.TimeZone).

Code Block

title	Example

...	
	String timeZoneID = getCRMSession().getTimezone();
	TimeZone timeZone= TimeZone.getTimeZone(timeZoneID);
	Date currentDate = DateUtil.getCurrentDate(timeZone);	
...

Expand

title	Time zone converting

convertDate(Date date, String fromTimeZone,String toTimeZone) method converts the given date from one time zone to another.

Code Block

title	Example

...	
    //The date is converted to UTC time zone
	Date date1 = DateUtil.convertDate(date1,getCRMSession().getTimezone(), "UTC");
		
...

Handling SQL Dates

Expand

title	Get SQL Date

getSelectSQLDate(String dbType, String datefield) method returns a script which is the given date or date field in the correct form for the given the database type. It works for both SQL and Hibernate queries.

Code Block

title	Hibernate Query Example

public ArrayList<CRMDO> loadEffective(Date asOfDate){
	ArrayList<Object> values = new ArrayList<Object>();
	String dbType = getCRMSession().getDbtype();
	if(expirationDate!=null)
	{
		values.add(asOfDate);
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate")+"<=:p"+values.size();
		criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate")+">:p"+values.size();
	}
	
	return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
				values,
				getDefaultAssociations(),
				null,
				null);	
	
}

Code Block

title	SQL Query Example

public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
		String dbtype = getCRMSession().getDbtype();

		String sql = 
						"\n 	SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
						"\n 	WHERE RT.RENTTYPEID = ?" +
						"\n     AND PV.PRODVALIDITYDELETED = 0 " +
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) + 
						"\n 	AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) ;
					
 
		ArrayList<Object> parameters = new ArrayList<Object>();
		parameters.add(rentalType.getId());

		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
		{
			return new Boolean(true);
		}
 
	return new Boolean(false);
}

...

Versions Compared

Old Version 15

New Version 16

Key

Handling SQL Dates