Search between two dates

[Former Member]

Hi,

I have a fields start_date(Literal Date) and end date (Literal Date) in my MDM table.

Could any one give me a clue how to search values between these two dates from java program to this type of columns.

Waiting for reply.

[Former Member]

Hi,

Following is a snippet of code for searching a range on a date/time field in an MDM repository.

In the following code, the table we are searching on is called "Products", and has two fields: "Part Number" and "Update Date" which is the date field. We will search for all products with "Update Date" between 2006-1-1 and 2006-1-31. (Remember in Java that MONTHS are from 0-11, and NOT 1-12).

============= START OF CODE =================

// Create a ResultSetDefinition

ResultSetDefinition rsd = new ResultSetDefinition("Products");

rsd.AddField("Part Number");

rsd.AddField("Update Date");

// Create a Search object

Search search = new Search("Products");

FreeFormTableParameter fftp = search.GetParameters().NewFreeFormTableParameter("Products");

FreeFormParameterField ffpf = new FreeFormParameterField("Update Date");

FreeFormParameter ffp = new FreeFormParameter();

ffpf.Add(new DateTimeParameter(2006, 0, 1, 0, 0, 0, 0, FreeFormParameter.GreaterThanOrEqualToSearchType));

ffpf.Add(new DateTimeParameter(2006, 0, 31, 0, 0, 0, 0, FreeFormParameter.LessThanOrEqualToSearchType));

ffpf.SetSearchOperator(FreeFormParameterField.SEARCH_OPERATOR_AND);

fftp.Add(ffpf);

// Get the results using the search

A2iResultSet rs = catalog.GetResultSet(search, rsd, "Part Number", true, 0);

System.out.println("Num found = " + rs.GetRecordCount());

============= END OF CODE =================

Hope this helps,

Walter

[Former Member]

in Data Manager Version 5.5 SP5 a field of type Literal Date has a dropdown with several operators in the Free-Form Search. They are ABS=, ABS<, Range, etc..

Does the API have these Enums? Or if I want to do a range do I still have to do it like you describe Walter?

[Former Member]

If you are using dates in SQL you can use operators like <,= and > to select data between a date range.

For example, "select * from <table> where start_date >='start_date' and end_date <='end_date'" will get you records starting from 'start_date' to 'end_date'.

You may also use SQL keyword 'BETWEEN'

Feel free to email me should you need detailed SQL Scripts.

--

Venkat