How to search in the Contact Search Center (CSC) for specific dates - for example records with Birthdays in a certain month?
- Have a user defined date field for example UDOB - Date of Birth in the Contact Set with a variety of date values
[STEPS TO REPLICATE]
1. Browse to a contact record where the Date of Birth is filled (for example Jun 09 1996) or depending on how user defined dates are displayed as 6/9/1996 (also may depend on current set regional settings)
2. Double click on the user defined data field, which opens the Contact Search Center
3. The default condition shows Date of Birth (UDOB) Begins with 19960609
>> RESULT: Although the underlying record has a Date of Birth set to 6/9/1996 it will not appear in the list
- Most options and ways to enter a date are failing and not providing the expected results for example entering '06' , '6' , %06% in order to search for all months June or also anything in format YYYYMMDD or YYYYDDMM, either too many or no records at all are found.
- Users may have the impression that only searching for a year does work as the results seem to be correct when starting with 1996
- GoldMine Premium converts the date field into a character field with the following convert statement: convert(char,c2.UDOB,20) which can be seen in a SQL trace
SELECT TOP 500 c1.ACCOUNTNO,c1.STATUS,c1.OWNER,c1.LASTNAME,c1.COMPANY,c1.CONTACT,c1.PHONE1,c1.STATE,c1.ZIP,c2.Udob,c1.CITY,c1.KEY1,c1.KEY2,c1.KEY3,c1.KEY4,c1.KEY5,c1.RECID FROM GMPE201410412LARGEUSDEMO.DBO.CONTACT1 c1 WITH (NOLOCK) inner join GMPE201410412LARGEUSDEMO.DBO.CONTACT2 c2 WITH (NOLOCK) on ( c1.ACCOUNTNO = c2.ACCOUNTNO ) WHERE ( convert(char,c2.UDOB,20) LIKE '6/9/1996%' ) ORDER BY isnull(c2.Udob,''),c1.RECID
>> This converts the date values in the following format -> 'YYYY-MM-DD hh:mm:ss'.
Note beside: In initial tests this was confirmed when using an US-EN setup environment, with US regional settings and US-EN Microsoft SQL Server, but in verification also then on a GERMAN (DE-DE) System, with German regional settings and a German (DE-DE) Microsoft SQL Server
- This means that a Date of Birth field
a. May 04, 2009 would be converted to '2009-05-04 00:00:00'
b. Apr 28, 2009 would be converted to '2009-04-28 00:00:00'
c. Jun 09. 1996 would be converted to '1996-06-09 00:00:00'
- In order to search in the Contact Search Center for specific dates, the user 'simply' needs to apply the specific date format for example for a JUNE search the middle part -06- as this part is unique in the converted data.
- This is unique as years are always 4 digits long like 2009 and days have ONLY a leading '-'.
- Applying the same logic for days does not work immediately as it needs to be additionally considered that when entering '%-09 ' in the CSC the blank will not be considered as a blank and the user will receive also all September records, therefore a day search must be extended to a search value as '%-09 0' as this is again unique.
With this information users can now search for certain years, certain month or certain days in the CSC ('Begins with' criteria as default) as each part of this would be unique.
- No leading % necessary, simply enter YYYY format
- This is unique as never 4 numeric characters appear without any '-' or ':' separator.
- Examples when searching for years
201% >> all records with years from 2010 -2019
19 >> all records with years from last century
- Leading % is necessary when the year is not relevant for example only contacts born in June regardless of the year + the desired month in the format '-MM-'
- This is unique as there is no other part surrounded by '-'
- Examples when searching for months
%-06-% >> all records where the date field(month) is June
%-12-% >> all records where the date field(month) is December
2009-08 >> all records where the date field(year) is 2009 and where the date field(month) is August
%-0%- >> all records where the date field(month) is in Jan, Feb, March, Apr, May, Jun, Jul, Aug, Sept
%-1%- >> all records where the date field(month) is in Oct, Nov, Dec
- Leading % is necessary when the year and the month is not relevant for example only contacts born on the first of any month or year + '-DD 0'
- This is unique as on no other part a ' 0' follows. In this case it is necessary to enter ' 0' as otherwise also the matching months will be found (for 01 -12)
- Examples for searches
%-10 0% >> all records where the date field(day) is the 10th, all days up to the 13th day require the additional ' 0' as otherwise any contacts will be in the result where the month is also matching
%-31 >> all records where the date field(day) is the 31st . In this case we don't need the ' 0' as there exists no 31st month.
Searching for an exact match for specific days in CSC
- Despite the fact that when opening the CSC via a double-click on the date field the very first value appears in format 'YYYYMMDD' also searching for exact days requires the above format 'YYYY-MM-DD' in order to find records
- Examples for exact matching records
1996-06-09 >> for any records with a Date of Birth on June 09, 1996
2009-04-29 >> for any records with a Date of Birth on April 29, 2009
2009-07-04 >> for any records with a Date of Birth on July 04, 2009