September 23, 2003 at 11:02 am
I am having a problem coming up with a line of code. I am trying to return a list of quotes that are due the next day (this is for a shop alert that will be ran every day to warn people of quotes that need to get done)
Any suggestions?
Select Quote_ID from Quote where Due_Date..........
September 23, 2003 at 12:07 pm
Try something along this line:
WHERE Due_Date > DATEADD(HOUR,0,DATEADD(DAY(DATEDIFF(DAY,0,GETDATE()),0))
AND Due_Date < DATEADD(HOUR,0,DATEADD(DAY(DATEDIFF(DAY,-1,GETDATE()),0))
-SQLBill
September 26, 2003 at 7:25 am
When I go to run that code, I get an error saying "The day function requires 1 argument", any suggestions??
September 26, 2003 at 7:49 am
quote:
When I go to run that code, I get an error saying "The day function requires 1 argument", any suggestions??
If there is an index on Due_date, you could use:
WHERE Due_Date >= CONVERT(char(8),@GETDATE()+1,112) AND Due_Date < CONVERT(char(8),@GETDATE()+2,112)
otherwise:
WHERE DATEDIFF(d,GETDATE(),Due_Date) = 1
If all the values are guaranteed to contain no (i.e. zero or midnight) time component, then just:
WHERE Due_Date = CONVERT(char(8),@GETDATE()+1,112)
--Jonathan
Edited by - jonathan on 09/26/2003 2:35:10 PM
Edited by - jonathan on 09/26/2003 7:19:02 PM
--Jonathan
September 26, 2003 at 7:57 am
Typo on my part. There should have been a comma after the first DAY in each line:
WHERE Due_Date > DATEADD(HOUR,0,DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE()),0))
AND Due_Date < DATEADD(HOUR,0,DATEADD(DAY,(DATEDIFF(DAY,-1,GETDATE()),0))
-SQLBill
September 26, 2003 at 9:10 am
OK, I dont know if I explained this right. This will not be a report that someone can click on, choose a date and generate a report. This is for a shop alert that I am trying to set up in our system that takes the current day and generates a list of quotes that are due the next day. This will happen automatically every morning without anyone having control over choosing the date to compare with. So I am not sure if GETDATE is going to work. Is there an SQL way of saying "tomorrow":), I know it sounds pretty amateur, but in essence, that is what I want
September 26, 2003 at 11:09 am
quote:
OK, I dont know if I explained this right. This will not be a report that someone can click on, choose a date and generate a report. This is for a shop alert that I am trying to set up in our system that takes the current day and generates a list of quotes that are due the next day. This will happen automatically every morning without anyone having control over choosing the date to compare with. So I am not sure if GETDATE is going to work. Is there an SQL way of saying "tomorrow":), I know it sounds pretty amateur, but in essence, that is what I want
Yes, it will work. GETDATE() evaluates to the current date (and time), so DATEADD(d, 1, @getdate-2()) will always be tomorrow (at the same time) if your system clock is correct.
--Jonathan
Edited by - jonathan on 09/26/2003 11:09:16 AM
--Jonathan
September 26, 2003 at 11:42 am
All right, got it, it works, cool, thank you so much!!!
Now, another thing that I just thought of, On Fridays, it needs to return the values for the following Monday, (we don't do business on weekends). Can you help me with that one?
September 26, 2003 at 12:40 pm
Something like this, you'll have to work with it:
SELECT
CASE WHEN DATEPART(dw,getdate()) = 5
THEN <code>
ELSE <code>
DATEPART returns an integer showing which day of the week it is.
1 = Monday, 2 = Tuesday, etc. So 5 = Friday.
The CASE will run one code if today is Friday another if it is any other day.
-SQLBill
September 26, 2003 at 4:01 pm
quote:
All right, got it, it works, cool, thank you so much!!!Now, another thing that I just thought of, On Fridays, it needs to return the values for the following Monday, (we don't do business on weekends). Can you help me with that one?
DECLARE @Today datetime, @NextBusinessDate datetime
SET @Today = CONVERT(char(8),GETDATE(),112)
SET @NextBusinessDate = CASE
WHEN DATEPART(dw,@Today) IN ((@@DATEFIRST+1)%7^7,@@DATEFIRST%7^7)
THEN @Today + ((@@DATEFIRST+1)%7^7 + 3) - DATEPART(dw,@Today)
ELSE @Today + 1 END
...
WHERE Due_Date >= @NextBusinessDate AND Due_Date < @NextBusinessDate + 1
--Jonathan
--Jonathan
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply