+ Start a Discussion
mavsmavs 

URGENT!!!!Date Filter

Hello -

 

I looked at the SOQL documentation. But i did not find...please help

 

if i write Select id,Name where expiration_date__c>=TODAY, it is working. but TODAY will not consider current Time.

 

How to write query to grab fields where datetime_field>=System.NOW() ?

 

Select id,Name where expiration_date__c>=System.NOW()

 

 

expiration_date__c is of type DateTime.

I want to display id and Name values where current datetime is not passed the expiration_date__c.

 

Please advise...Thanks

Message Edited by mavs on 04-14-2009 11:22 AM
Message Edited by mavs on 04-14-2009 12:25 PM
Best Answer chosen by Admin (Salesforce Developers) 
SuperfellSuperfell

Its not a SOQL keyword, its an apex function, so you have to bind to it,

 

[select Name,expiration_date__c from Object_Test__c where expiration_date__c>= :System.Now() and  UID__c=:UserInfo.getUserId() limit 1] 

All Answers

KevinLaurenceKevinLaurence

Actually, TODAY does include the time. The Force.com Web Services API Developer's Guide defines TODAY as

 

"Starts 12:00:00 of the current day and continues for 24 hours."

 

The documentation also states:

 

"A fieldExpression can use a date literal to compare a range of values to the value in a date or dateTime field. Each literal is a range of time beginning with midnight (12:00:00). To find a value within the range, use =. To find values on either side of the range, use > or <."

 

Don't forget that that dateTime field values are stored as Coordinated Universal Time (UTC). More information is available in the documentation.

mavsmavs

Hi

Thanks for the solution. 

I have one more question though.

 

 my expiration_date__c =5/31/2009 3:00 PM  (EST)

 

Then how can i restrict users (PST,mountain and central) to only access or view data upto Eastern timezone?

 

For example, since the expiration date is as shown above. Users at PST time zone can view data till 5/31/2009 12:00PM (PST)

 

Please let me know if it is unclear...thanks

 

Please advise

 

 

KevinLaurenceKevinLaurence

As the documentation states:
 

"When you specify a date in a SOQL query, it can be a specific date, or a date literal, which is a fixed expression representing a relative range of time such as last month or next year. Remember that dateTime field values are stored as Coordinated Universal Time (UTC). When one of these values is returned in the Salesforce application, it is automatically adjusted for the timezone specified in your organization preferences. Your application may need to handle this conversion."

 
So, you will need to use the default time for your Salesforce organization.

mavsmavs

I confirmed that TODAY does not include Time.

 

 I have 3 records for me. For all the 3 records expiration_date=4/15/2009 9:58 AM. And My Time Zone in the User table is (GMT-05:00) Eastern Daylight Time (America/New_York)

 

 

This is my query.

[select Name,expiration_date__c from Object_Test__c where expiration_date__c>=TODAY and  UID__c=:UserInfo.getUserId() limit 1]

 

The above query is returning me 3 records. If TODAY includes time the above query should actually return 0 records. I tried this after 9:58 AM this morning.

 

 

Please advise how to query with the current DateTime.....i have very urgent requirement which needs to be done based on the current DateTime.,.....please advise

Message Edited by mavs on 04-15-2009 08:32 AM
SuperfellSuperfell
If you're using the web services API you will have to pass in the current literal value on now from your client code. If you're using apex, you can use System.now and bind the filter value to that.
mavsmavs

I am using Apex.

 

System.Now did not work either.

 

[select Name,expiration_date__c from Object_Test__c where expiration_date__c>=System.Now and  UID__c=:UserInfo.getUserId() limit 1]

 

Compile Error: unexpected token: System.Now

SuperfellSuperfell

Its not a SOQL keyword, its an apex function, so you have to bind to it,

 

[select Name,expiration_date__c from Object_Test__c where expiration_date__c>= :System.Now() and  UID__c=:UserInfo.getUserId() limit 1] 

This was selected as the best answer