+ Start a Discussion
magandrezmagandrez 

Date/Datetime problem in SOQL

Hi all,

 

I have the following code that doesn't generate a proper SOQL sentence. I'm passing a date from a VF page standard date selector, I needed to create a query, but the query doesn't work properly. I think it is because of the Date/Datetime format, but after try all posibilities, it doesn't work as it should (the query doesn't work).

 

public generateRestSched(ApexPages.StandardController controller) {

 

String room = ApexPages.currentPage().getParameters().get('id');
bookingdate = date.parse(ApexPages.currentPage().getParameters().get('date'));
system.debug('###############################'+bookingdate);
 

entries = [SELECT Reservation_Time_Start__c,
   End_Time__c,
   Customer_and_Event_Info__c,
   Booking_Date__c,
   Additional_Information__c,
   Room_del__c
FROM Room_Reservations__c
WHERE Booking_Date__c =:bookingdate
AND Room_del__c=:room];

 

system.debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&'+entries);

}

 

 

Any ideas of what is going on?

 

Thanks!,

 

MGA.

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

Looking at the Apex docs, date.parse probably won't like that format.

 

If it were me I'd pull the date components from the string and construct the date by hand.  Something like:

 

String dateStr=ApexPages.currentPage().getParameters().get('date');

Integer year=dateStr.substring(0, 4);
Integer month=dateStr.substring(5, 7);
Integer day=dateStr.substring(8, 10);
bookingdate = Date.newInstance(year, month, day);

 

The indices of the string might not be quite right, but hopefully you get the picture.

 

All Answers

bob_buzzardbob_buzzard

A few questions:

 

(1) What is the type of the Booking_Date__c field on the Room_Reservations__c record?

(2) What does the debug of the parsed booking date give you?

(3) What format is the date present in the URL parameters

(4) Do you get an error or just no matches?

magandrezmagandrez

Hi bob,

 

1) The data type of Booking_Date__c is Date.

2) The debug of 'bokingdate' gives this for booking date:

 

10:07:27:282 USER_DEBUG [9]|DEBUG|###############################2012-04-24 00:00:00

 

and empty for entries:

 

10:07:27:305 USER_DEBUG [21]|DEBUG|&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&()

 

If I debug 'room' gives me the right ID passed from VF Page

 

 

10:07:27:283 USER_DEBUG [11]|DEBUG|%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%a0JR0000003rGMV

 

3) The date in the URL goes like this: 

 

/apex/getRestaurantSchedule?id=a0JR0000003rGMV&date=24.4.2012

 

4) No matches (and I double checked that there exist records for that date and that room).

 

I hope it clarifies a bit.

 

MGA.

bob_buzzardbob_buzzard

Looking at the Apex docs, date.parse probably won't like that format.

 

If it were me I'd pull the date components from the string and construct the date by hand.  Something like:

 

String dateStr=ApexPages.currentPage().getParameters().get('date');

Integer year=dateStr.substring(0, 4);
Integer month=dateStr.substring(5, 7);
Integer day=dateStr.substring(8, 10);
bookingdate = Date.newInstance(year, month, day);

 

The indices of the string might not be quite right, but hopefully you get the picture.

 

This was selected as the best answer
magandrezmagandrez

Hi bob,

 

The idea seems good, but how do you cast the String dateStr to those integers year, month and day?

 

MGA.

magandrezmagandrez

Answering myself:

 

integer.valueOf(dateStr.substring(0,4));

 

I will try this idea and see if it works.

magandrezmagandrez

Hi, 

 

Now seems to work...but even though there are results in the database, it doesn't retrieve anything. Any idea of what is happening? The code looks like this now:

 

public generateRestSched(ApexPages.StandardController controller) {
String room = ApexPages.currentPage().getParameters().get('id');
strDate = ApexPages.currentPage().getParameters().get('date');

integer day = integer.valueOf(strDate.substring(0,2));
integer month = integer.valueOf(strDate.substring(3,5));
integer year = integer.valueOf(strDate.substring(5,9));
bookingdate = Date.newInstance(year,month,day);


system.debug('###############################'+day);
system.debug('%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'+month);
system.debug('///////////////////////////////'+year);

entries = [SELECT Reservation_Time_Start__c,
   End_Time__c,
           Customer_and_Event_Info__c,
   Booking_Date__c,
   Additional_Information__c,
   Room_del__c
FROM Room_Reservations__c
WHERE Booking_Date__c =:bookingdate
AND Room_del__c=:room];
system.debug('&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&'+entries);
}

 

MGA.

bob_buzzardbob_buzzard

What does the debug output?  Also, what does the booking date look like if you dump it out?

magandrezmagandrez

Hi,

 

Thanks for your answer. 

 

The last problem was caused by using a wrong ID. Once fixed that, it worked out smoothly.

 

Many thanks for your help, bob.

 

MGA.