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	Adjust server date based on client time zone

adjustServerDateBasedOnClientTimeZone(Date val, String organisationTimeZone,HttpServletRequest request) method adjusts the server date based on the client's time zone.

Code Block

title	Example

...	
  	SuperSession ss=WebUtil.getSuperSession(request);	//request object is of type javax.servlet.http.HttpServletRequest
	serverDate = DateUtil.adjustServerDateBasedOnClientTimeZone(serverDate,ss.getTimezone(),request);
...

Expand

title	Adjust client date based on server time zone

adjustClientDateBasedOnServerTimeZone(Date val, String organisationTimeZone,HttpServletRequest request) method adjusts the cient date based on the server's time zone.

Code Block

title	Example

...	
  	SuperSession ss=WebUtil.getSuperSession(request);	//request object is of type javax.servlet.http.HttpServletRequest
	clientDate = DateUtil.adjustClientDateBasedOnServerTimeZone(clientDate,ss.getTimezone(),request);
...

Handling SQL Dates

Expand

title	Get SQL Date

getCurrentSQLDate(String dbType, String datefield) method returns the date format of current date based on DB type

Code Block

title	Hibernate Query Example

/**
	 * Checks if a billing term scheme is used by a billing term.
	 * 
	 * @param billingTermScheme - the billing term scheme to check
	 * @return true if a billing term scheme is used by a billing term, false if not
	 * @throws Exception
	 */
	public Boolean isUsedByBillingTerms(CRMDOBillingTermScheme billingTermScheme) throws Exception {
		
		ArrayList<Object> parameters = new ArrayList<Object>();
		
		String sql = 
				"\n SELECT 'x' FROM BILLINGTERMS " +
				"\n WHERE BTDELETED = 0 " +
				"\n AND " + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEFFECTIVEDATE") + "<=" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n AND ((" + DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), "BTEXPIRATIONDATE") + ">" + DateUtil.getCurrentSQLDate(getCRMSession().getDbtype(),getCRMSession().getTimezone()) +
				"\n		OR BTEXPIRATIONDATE IS NULL)) " +  
				"\n AND BTSCHEMECODE = ?";
	
		parameters.add(billingTermScheme.getCode());
		
		if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, getCRMSession().getDbtype()))
		{
			return new Boolean(true);
		}
		else
		{
			return new Boolean(false);
		}
	}

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 andHibernate 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 18

New Version 19

Key

Handling SQL Dates