asked on

Need assistance on GETDATE in Delphi SQL search

I have a search that returns the results I want EXCEPT if there are no job records with MfgStartDate matching Today's Date (result from the GETDATE)

How can I update the attached search to find what it currently finds (because it works well as long as there are records that have today as a start date) and if RecordCount = 0, use the next available date in the table that is greater than today's date?

For example, if its Sunday Oct 23rd, the date it uses to search for the jobs should be Monday Oct 24th IF there are no job records that have Oct 23rd as its Start Date.

Thank you!

//find first job for today and set JobSchedule = 0 AS Total
  with dmMfgQuantities.qrySearch do begin
     Close;
     sql.Clear;
     sql.Add('SELECT TOP(1)JobID, JobSchedule FROM MfgSchedulingView WHERE (Mfg_Start = CONVERT(datetime, CONVERT(char(12), GETDATE()))) ORDER BY Mfg_Start, Orders');
     Open;
     sJobID := FieldValues['JobID'];
  end;

Open in new window

8080_Diver

Try the following:

SELECT TOP(1)
    JobID
  ,JobSchedule
FROM MfgSchedulingView
WHERE Mfg_Start >= CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(), 120), 120) 
  AND Mfg_Start < CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE() + 1, 120), 120)

Open in new window

CONVERT requires a format indicator when converting dates. Also, because a Date only column in SQL Server has an implicit time of 00:00:00.000, if you try to match on just the date protion and your entry has time elements, you'll get no matches unless a row just happens to have a time of 00:00:00.000 (which is somewhat unlikely ;-).. Similarly, if you try to match a date-only to a GETDATE() value, because GETDATE() includes the time, once again you'll get tno results.

Norm-al

ASKER

Will this work on a Saturday if the next Start Date is on Monday?

8080_Diver

In the strictest sense, yes . . . however, it would probably not get the data you want if you want to get everything between one start date and then next one and someone does something on Sunday.

This is one of those spots where you need to provide all pertinent requirements up front rather than nickle and dime us with requirements. ;-)

Do you also need to allow for holidays?

If the query is executed on Saturday, do you need from 00:00 on Saturday through 23:59:59.999 on Sunday?

If Monday is a holiday, do you need Saturday 00:00 through Monda 23:59:59.999?

In other words, what are ALL of the rules? ;-)

Geert G

why are you doing a double conversion ?
Mfg_Start = CONVERT(datetime, CONVERT(char(12), GETDATE())))

CONVERT(datetime,
and
CONVERT(char(12),

?????

Norm-al

ASKER

We have a tbl.Calendar with WorkDays (Closure, Holiday, Weekend, WorkDay) and CalendarDays (1/1/11). I want the next day to be the next available CalendarDays where WorkDays = WorkDay.

8080_Diver

If you are driving this off of a Calendar table that will provide you with Workdays, then you should be able to get the "next" work day without any conversions by selecting the MIN(WorkDay) > Mfg_Start AND MIN(Workday) > GetDate() .

In other words, if your Mfg_Start is '2011-10-21' and the Calendar table has '2011-10-24' as the next work a WorkDay and you are running this at 15:30 (3:30pm) on '2011-10-21', then the next Workday that is greater than both the Mfg_Start and the current datetime will be a '2011-10-24'. That will also be true if you run the query on Saturday, 2011-10-22 or Sunday 2011-10-23. If 2011-10-24 were to be a holiday (for whatever reason), then the next work day after 2011-10-21 would be 2011-10-25 9assuming your Calendar table is set up reasonably ;-) and that would be the dat that the query would return.

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ephraim Wangoya

OR SIMPLIFY TO

//find first job for today and set JobSchedule = 0 AS Total
  with dmMfgQuantities.qrySearch do begin
     Close;
     sql.Clear;
     sql.Add('DECLARE @START DATETIME, @END DATETIME');
	 sql.Add('SET @START = DATEADD(DD, 0, DATEDIFF(dd, 0, GETDATE()))');
	 sql.Add('SET @END = DATEADD(DD, 1, @START)');
	 sql.Add('SELECT TOP(1)JobID, JobSchedule FROM MfgSchedulingView WHERE (Mfg_Start BETWEEN @START AND @END) ORDER BY Mfg_Start, Orders');
     Open;
     sJobID := FieldValues['JobID'];
  end;

Open in new window

8080_Diver

ewangoya,

What happens when the StartDate is Friday and both Saturday and Sunday are not "WorkDays"? How about if the StartDate is Thrusday and not nly are Saturday and Sunday not "Work Days" but Friday is a holiday?

That's why the Calendar table exists, dude. ;-)