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

Norm-alNetwork EngineerAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:

Even better
//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 BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()))) ORDER BY Mfg_Start, Orders');
     Open;
     sJobID := FieldValues['JobID'];
  end;

Open in new window

0
 
8080_DiverCommented:
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.    
0
 
Norm-alNetwork EngineerAuthor Commented:
Will this work on a Saturday if the next Start Date is on Monday?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
8080_DiverCommented:
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? ;-)
0
 
Geert GOracle dbaCommented:
why are you doing a double conversion ?
Mfg_Start = CONVERT(datetime, CONVERT(char(12), GETDATE())))

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

?????
0
 
Norm-alNetwork EngineerAuthor Commented:
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.
0
 
8080_DiverCommented:
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.
0
 
Ephraim WangoyaCommented:
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

0
 
8080_DiverCommented:
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. ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.