0

I am using an SOQL in a webservice that is querying the Contact object based on the BirthDate field. The webservice request has the date in the format of 'yyyy-MM-dd',i.e.,1962-01-29 as an example.

I am not able to use this value to query the Contact Object. I tried converting this String Date into Date object. However, it always has 00:00:00 at the end. I tried the below options.

String strDate = '1962-01-29';
Date birthDate = Date.valueOf(strDate) ==> This results in 1962-01-29 00:00:00

String strDate = '1962-01-29';
Date dob = Date.valueOf(strDate)
Datetime dt = Datetime.newInstance(dob.Year(),dob.Month(),dob.day());
Date birthDate = dt.date() ==> This also results in 1962-01-29 00:00:00

How do I construct a Date object (without time) from the string date '1962-01-29'?

I did search for solutions in various forums and most of them are suggesting the above two solutions only, which does not work. I am not sure whether this behavior was changed in some Salesforce's releases.

Below is the full query that I am using.

                String query = 'SELECT id,' +
                                        'LastName,' +
                                        'FirstName,' +
                                        'BirthDate,' +
                                        'MiddleName__c,' +
                                        'MailingAddress,' +
                                        'OtherAddress,' +
                                        'EmploymentStatus__c,' +
                                        'Suffix__c,'+
                                        'Investment_Experience__c ' +
                                'FROM Contact WHERE';
                // Fetching the details of contact based on LastName,FirstName,BirthDate
                if(string.isNotEmpty(lastName)){ //Checking for last name value
                  if(lastName.contains('*') || lastName.contains('%')){
                    lastName = (lastName).replace('*','%');
                    query += ' LastName LIKE \''+lastName+'\' AND';
                  }
                  else{
                    query += ' LastName = \''+lastName+'\' AND'; 
                  }
                }
                if(string.isNotEmpty(firstName)){
                 if(firstName.contains('*') || firstName.contains('%')){
                    firstName = (firstName).replace('*','%');
                    query += ' FirstName LIKE \''+firstName+'\' AND';
                  }
                  else{
                    query += ' FirstName = \''+firstName+'\' AND';   
                    }
                }
                if(string.isNotEmpty(dateOfBirth)){
                    //Datetime dt = Date.valueOf(dateOfBirth);
                    //Datetime dob = Date.newInstance(dt.year(), dt.month(), dt.day());
                    query += ' BirthDate = ' + Date.valueOf(dateOfBirth) + ' AND';
                }
                query += ' Account.OrgNumber__c IN (\''+(string.join(lstOrganizationNumbers,'\',\''))+'\') ';
                query += ' ORDER BY lastName,firstName,BirthDate';
                query += ' LIMIT 50';
                system.debug('Query:: '+query); 
4

An instance of Date has no Time attributes, unlike Datetime. However, if you debug it, the output will show one. This debugging result has no impact on how the value will be used in SOQL.

Your problem is not how you are constructing your date, but likely how you construct your SOQL (which you haven't shown us). If you are building a dynamic query in Apex, you can just bind to a variable in memory (as long as you use no dot reference):

Date dateValue = Date.valueOf('1962-01-29');
String soql = 'SELECT Id FROM My_Object__c WHERE Date_Field__c = :dateValue';
2

I believe there's something wrong with your query. This is your query:

SELECT Id, FirstName, LastName, BirthDate FROM Contact where BirthDate = 1962-01-29 00:00:00 AND LastName LIKE 'Smith%'

You cannot write the date as a literal(with or without single quotes): 1962-01-29 00:00:00

If you're having trouble with Date Datetime comparissons try to use a dummy range like this one:

Date d = Date.parse('2/1/2019');
List<Account> accounts = [SELECT Id FROM Account WHERE CreatedDate > :d.addDays(-1) AND  CreatedDate < :d.addDays(+1)];
System.debug('Result: ' + accounts);

In simple words: Get all the Accounts that were created after yesterday and before tomorrow. Get all accounts from today (or any day between the range).

I highly advise you to go through this Trailhead: https://trailhead.salesforce.com/en/content/learn/modules/apex_database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.