Date Comparison In Where Clause

Epicor ERP 10
1

I’ve been wracking my brain over getting my date comparison to work with a where clause for GetRows on a Job Adapter.

With this where clause:
string whereJob = “StartDate >= DATEADD(hour,-2,GETDATE())”;

I am getting the following error:
Inner Exception Message: Error accessing the database: ‘JobHead.hour’ is not a recognized dateadd option.
(note if I try to use abbrievated hh instead of hour, I get same error with ‘JobHead.hh’)

Why is JobHead being prepended to that parameter?

If I simply use string whereJob = “StartDate >= GETDATE()”; it works

2

It thinks hour and hh are fields in the jobhead table. Looks like first
parameter should be field name.

3

Is this in a customization?

4

Yes it’s a customization: (making my own implementation for selecting job in MES)

oTrans_jobAdapter.BOConnect();
SearchOptions optsJob = new SearchOptions(SearchMode.AutoSearch); 
optsJob.PageSize = 2;
string whereJob = "StartDate >= GETDATE() and JobEngineered = TRUE and JobReleased = TRUE and JobClosed = FALSE";
optsJob.NamedSearch.WhereClauses.Add("JobHead",whereJob); 
var jobs = oTrans_jobAdapter.GetRows(optsJob,out more);
epiUltraGridJobs.DataSource = jobs;

@deschuerman is it not the standard SQL function? If not, do you have the function prototype you could share?

5

Try this, might not be entirely correct as I just typed it here, but it should get you started. The {0} might need to be in single quotes.

string whereJob = string.Format(“StartDate >= {0} and JobEngineered = TRUE and JobReleased = TRUE and JobClosed = FALSE”, DateTime.Now.AddDays(-2).ToShortDateString());

1 Like
6

A couple of things, I don’t think StartDate carries the hours… so even if you were to get around that issue it won’t matter…

You want to do StartDate = GETDATE() AND StartHour > XXXX where XXXX is the hour you want

1 Like
7

Thanks for the suggestions. I’ll try both and report my results.

@josecgomez.trigemco I wasn’t completely sure how the comparison was going to work because when I print the StartDate it shows 12/22/2016 12:00

Update: @tkoch Right, wrong or otherwise, your method did solve the initial problem.

8

Yeah it’s a date time field but epicor stores the times separately “always” sorta

9

I thought I was further along than I really was - apparently the adapter I am using doesn’t give me the resource :disappointed:

Update: Crisis averted. Simply changing the adapter worked great. Thanks Epicor. I was worried I was going to have to combine data from 2 different data sources but it seems the JobOperSearchAdapter has it all - my new favorite adapter. Sorry European plug adapter I have to let you go:
https://encrypted-tbn3.gstatic.com/images?q=tbn:ANd9GcSrJW8G442A_eblCAZdWN4WTcFV1b0eeqBaiJuogDnqwJxRowjt