RE: Selecting a date range where date parts are multiple columns
RE: Selecting a date range where date parts are multiple columns
- Subject: RE: Selecting a date range where date parts are multiple columns
- From: <email@hidden>
- Date: Fri, 14 Jan 2005 10:19:42 +0100
- Thread-topic: Selecting a date range where date parts are multiple columns
Hi!
I am working on a similar database design. Actually we are not working with dates, but with month long periods. The database stores the year and month in two separate columns.
Here's the code:
// Public class methods
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects valid at the specified period.
*
* @param annee
* the year component of the period to match
* @param mois
* the month component of the period to match
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @return a fully bound qualifier
*/
public static EOQualifier qualifierMatchingPeriod(
final String keyPath,
final Number annee,
final Number mois)
{
return _qualifierMatchingPeriod(keyPath, annee, mois);
}
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects valid at the specified period.
*
* @param annee
* the year component of the period to match
* @param mois
* the month component of the period to match
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @return a fully bound qualifier
*/
public static EOQualifier qualifierMatchingPeriod(
String keyPath,
EOQualifierVariable annee,
EOQualifierVariable mois)
{
return _qualifierMatchingPeriod(keyPath, annee, mois);
}
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects that intersect the specified span of periods.
*
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @param anneeDebut
* the year component of the starting period
* @param moisDebut
* the month component of the starting period
* @param anneeFin
* the year component of the ending period
* @param moisFin
* the month component of the ending period
* @return a qualifier appropriate for database qualification
*/
public static EOQualifier qualifierIntersectingPeriodSpan(
final String keyPath,
final Number anneeDebut,
final Number moisDebut,
final Number anneeFin,
final Number moisFin)
{
return _qualifierIntersectingPeriodSpan(keyPath, anneeDebut, moisDebut, anneeFin, moisFin);
}
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects that intersect the specified span of periods.
*
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @param anneeDebut
* the year component of the starting period
* @param moisDebut
* the month component of the starting period
* @param anneeFin
* the year component of the ending period
* @param moisFin
* the month component of the ending period
* @return a qualifier appropriate for database qualification
*/
public static EOQualifier qualifierIntersectingPeriodSpan(
String keyPath,
EOQualifierVariable anneeDebut,
EOQualifierVariable moisDebut,
EOQualifierVariable anneeFin,
EOQualifierVariable moisFin)
{
return _qualifierIntersectingPeriodSpan(keyPath, anneeDebut, moisDebut, anneeFin, moisFin);
}
// Private class methods
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects valid at the specified period.
*
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @param annee
* the year component of the period to match
* @param mois
* the month component of the period to match
* @return a qualifier appropriate for database qualification
*/
private static EOQualifier _qualifierMatchingPeriod(
final String keyPath,
final Object annee,
final Object mois)
{
if (keyPath != null && keyPath.length() > 0)
{
final String prefix = keyPath + NSKeyValueCodingAdditions.KeyPathSeparator;
final String anneeDebutKeyPath = prefix + ANNEE_DEBUT;
final String anneeFinKeyPath = prefix + ANNEE_FIN;
final String moisDebutKeyPath = prefix + MOIS_DEBUT;
final String moisFinKeyPath = prefix + MOIS_FIN;
return _qualifierMatchingPeriod(
anneeDebutKeyPath,
moisDebutKeyPath,
anneeFinKeyPath,
moisFinKeyPath,
annee,
mois);
}
else
{
return _qualifierMatchingPeriod(
ANNEE_DEBUT,
MOIS_DEBUT,
ANNEE_FIN,
MOIS_FIN,
annee,
mois);
}
}
private static EOQualifier _qualifierMatchingPeriod(
final String anneeDebutKeyPath,
final String moisDebutKeyPath,
final String anneeFinKeyPath,
final String moisFinKeyPath,
final Object annee,
final Object mois)
{
final EOQualifier beginLessThan =
new EOKeyValueQualifier(
anneeDebutKeyPath,
EOQualifier.QualifierOperatorLessThan,
annee);
final EOQualifier beginEqual =
new EOKeyValueQualifier(anneeDebutKeyPath, EOQualifier.QualifierOperatorEqual, annee);
final EOQualifier beginMonth =
new EOKeyValueQualifier(
moisDebutKeyPath,
EOQualifier.QualifierOperatorLessThanOrEqualTo,
mois);
final EOQualifier begin =
new EOOrQualifier(
new NSArray(
new EOQualifier[] {
beginLessThan,
new EOAndQualifier(
new NSArray(new EOQualifier[] { beginEqual, beginMonth }))
}));
final EOQualifier endGreaterThan =
new EOKeyValueQualifier(
anneeFinKeyPath,
EOQualifier.QualifierOperatorGreaterThan,
annee);
final EOQualifier endEqual =
new EOKeyValueQualifier(anneeFinKeyPath, EOQualifier.QualifierOperatorEqual, annee);
final EOQualifier endMonth =
new EOKeyValueQualifier(
moisFinKeyPath,
EOQualifier.QualifierOperatorGreaterThanOrEqualTo,
mois);
final EOQualifier end =
new EOOrQualifier(
new NSArray(
new EOQualifier[] {
endGreaterThan,
new EOAndQualifier(
new NSArray(new EOQualifier[] { endEqual, endMonth }))
}));
return new EOAndQualifier(new NSArray(new EOQualifier[] { begin, end }));
}
/**
* Creates a qualifier appropriate for database qualification of BasePeriod objects. <BR>
*
* The returned qualifier will match objects that intersect the specified span of periods.
*
* @param keyPath
* optional path leading the the BasePeriod object to qualify
* @param anneeDebut
* the year component of the starting period
* @param moisDebut
* the month component of the starting period
* @param anneeFin
* the year component of the ending period
* @param moisFin
* the month component of the ending period
* @return a qualifier appropriate for database qualification
*/
private static EOQualifier _qualifierIntersectingPeriodSpan(
final String keyPath,
final Object anneeDebut,
final Object moisDebut,
final Object anneeFin,
final Object moisFin)
{
if (keyPath != null)
{
final String prefix = keyPath + NSKeyValueCodingAdditions.KeyPathSeparator;
final String anneeDebutKeyPath = prefix + ANNEE_DEBUT;
final String anneeFinKeyPath = prefix + ANNEE_FIN;
final String moisDebutKeyPath = prefix + MOIS_DEBUT;
final String moisFinKeyPath = prefix + MOIS_FIN;
return _qualifierIntersectingPeriodSpan(
anneeDebutKeyPath,
moisDebutKeyPath,
anneeFinKeyPath,
moisFinKeyPath,
anneeDebut,
moisDebut,
anneeFin,
moisFin);
}
else
{
return _qualifierIntersectingPeriodSpan(
ANNEE_DEBUT,
MOIS_DEBUT,
ANNEE_FIN,
MOIS_FIN,
anneeDebut,
moisDebut,
anneeFin,
moisFin);
}
}
private static EOQualifier _qualifierIntersectingPeriodSpan(
final String anneeDebutKeyPath,
final String moisDebutKeyPath,
final String anneeFinKeyPath,
final String moisFinKeyPath,
final Object anneeDebut,
final Object moisDebut,
final Object anneeFin,
final Object moisFin)
{
final EOQualifier anneeFinLessThanQualifier =
new EOKeyValueQualifier(
anneeFinKeyPath,
EOQualifier.QualifierOperatorLessThan,
anneeDebut);
final EOQualifier anneeFinEqualQualifier =
new EOKeyValueQualifier(
anneeFinKeyPath,
EOQualifier.QualifierOperatorEqual,
anneeDebut);
final EOQualifier moisFinQualifier =
new EOKeyValueQualifier(
moisFinKeyPath,
EOQualifier.QualifierOperatorLessThan,
moisDebut);
final NSArray finQualifier =
new NSArray(
new EOQualifier[] {
anneeFinLessThanQualifier,
new EOAndQualifier(
new NSArray(
new EOQualifier[] { anneeFinEqualQualifier, moisFinQualifier }))
});
final EOQualifier anneeDebutGreaterThanQualifier =
new EOKeyValueQualifier(
anneeDebutKeyPath,
EOQualifier.QualifierOperatorGreaterThan,
anneeFin);
final EOQualifier anneeDebutEqualQualifier =
new EOKeyValueQualifier(
anneeDebutKeyPath,
EOQualifier.QualifierOperatorGreaterThan,
anneeFin);
final EOQualifier moisDebutQualifier =
new EOKeyValueQualifier(
moisDebutKeyPath,
EOQualifier.QualifierOperatorGreaterThan,
moisFin);
final NSArray debutQualifier =
new NSArray(
new EOQualifier[] {
anneeDebutGreaterThanQualifier,
new EOAndQualifier(
new NSArray(
new EOQualifier[] {
anneeDebutEqualQualifier,
moisDebutQualifier }))
});
return new EONotQualifier(
new EOOrQualifier(finQualifier.arrayByAddingObjectsFromArray(debutQualifier)));
}
Best,
Pierre
http://homepage.mac.com/I_love_my
-----Original Message-----
From: webobjects-dev-bounces+pierre.bernard=email@hidden
[mailto:webobjects-dev-bounces+pierre.bernard=email@hidden]On
Behalf Of Albert Jagnow
Sent: Thursday, January 13, 2005 8:33 PM
To: WebObjects (Group)
Subject: Selecting a date range where date parts are multiple columns
I am working with data from an old system and I am unable to alter the
table design. I have run across a rather strange table where the
designer of the table put the date information in multiple fields.
There is a year column a month column and day column. There is no one
column that contains complete date information. If I want to be able
to select records from this table that fall between two dates what is
the easiest way to do that?
--
Albert Jagnow
Web Developer
The University of Iowa Foundation
This e-mail (including any attachments) is covered by the Electronic
Communications Privacy Act, 18 USC. 2510-2521. It is confidential and
may be legally privileged. If you are not the intended recipient, you
are hereby notified that any retention, dissemination, distribution, or
copying of this communication is strictly prohibited. Please reply to
the sender that you have received the message in error, and then delete
it. Thank you.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden