# Date Comparison In Where Clause

**Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5)
**Created:** 2016-12-22 16:28 UTC
**Views:** 2573
**Replies:** 8
**URL:** https://www.epiusers.help/t/date-comparison-in-where-clause/36710

---

## Post #1 by @Chris_Conn

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

---

## Post #2 by @deschuerman

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

---

## Post #3 by @tkoch

Is this in a customization?

---

## Post #4 by @Chris_Conn

Yes it's a customization: (making my own implementation for selecting job in MES)
```cs
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?

---

## Post #5 by @tkoch

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());

---

## Post #6 by @josecgomez

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

---

## Post #7 by @Chris_Conn

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.

---

## Post #8 by @josecgomez

Yeah it's a date time field but epicor stores the times separately "always" sorta

---

## Post #9 by @Chris_Conn

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:
<img src="/uploads/default/original/1X/88643f90e95dd7a13a6b9d786f6176d738f5b6a0.png" width="259" height="194">https://encrypted-tbn3.gstatic.com/images?q=tbn:ANd9GcSrJW8G442A_eblCAZdWN4WTcFV1b0eeqBaiJuogDnqwJxRowjt

---


**Canonical:** https://www.epiusers.help/t/date-comparison-in-where-clause/36710
**Original content:** https://www.epiusers.help/t/date-comparison-in-where-clause/36710