?
Solved

Need assistance on GETDATE in Delphi SQL search

Posted on 2011-10-24
9
Medium Priority
?
486 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Bianca
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37021068
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
 

Author Comment

by:Bianca
ID: 37021120
Will this work on a Saturday if the next Start Date is on Monday?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37021328
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 37023289
why are you doing a double conversion ?
Mfg_Start = CONVERT(datetime, CONVERT(char(12), GETDATE())))

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

?????
0
 

Author Comment

by:Bianca
ID: 37033880
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37034013
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 37035508

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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37035525
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 37035883
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question