Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Obtaining the date in a Stored Proc

update Orders_Test set
Fulfilled = "NA",
AWP = NULL
WHERE FulFilled <> "NA" AND (GetDate() - OrderDate BETWEEN 60 AND 10000)

This is the statement I am trying to use but to no avail, I am assuming the date function is not right.

ANy help would be apprechiated
0
psilli1
Asked:
psilli1
  • 3
  • 2
  • 2
1 Solution
 
acampomaCommented:
is orderdata a datetime field??
0
 
acampomaCommented:
is orderdata a datetime field??
0
 
Brendt HessSenior DBACommented:
GetDate() - Orderdate will return a date, not an interval.  You want to use the DateDiff function:

...
WHERE FulFilled <> 'NA' And (DateDiff(d,OrderDate, GetDate()) BETWEEN 60 AND 10000)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
psilli1Author Commented:
Order date is a datetime field, I have used a similar method to obtain the date running via ASP.

DateDiff what is the d variable
0
 
psilli1Author Commented:
Here is the sp but it still doesn't work.

CREATE procedure sp_Tracking_Orders_Date_Update
as

DECLARE @error int

SELECT @error=0

BEGIN Transaction mytrans

update Orders_Test set
Fulfilled = 'NA',
AWP = NULL
WHERE FulFilled <> 'NA' AND (DateDiff(d,OrderDate, GetDate()) BETWEEN 60 AND 10000)

select @error=@@error
IF @error<>0
BEGIN
ROLLBACK TRANSACTION mytrans
END


COMMIT TRANSACTION mytrans

RETURN @error










0
 
Brendt HessSenior DBACommented:
In the DateDiff function, d is a flag indicating that you want to compare the number of days (as opposed to weeks, months, etc.)

You may be having a problem with the other part of your selection criteria.  If the value in Fulfilled is Null, your test will not return those records.  Also note that if you Rollback the transaction, you don't want to commit it later.  Try this instead:

BEGIN Transaction mytrans

update Orders_Test set
Fulfilled = 'NA',
AWP = NULL
WHERE (Fulfilled is Null OR FulFilled <> 'NA') AND (DateDiff(d,OrderDate, GetDate()) BETWEEN 60 AND 10000)

select @error=@@error
IF @error<>0
BEGIN
ROLLBACK TRANSACTION mytrans
END
ELSE
BEGIN
COMMIT TRANSACTION mytrans
END


One more possibility is to try this select:

WHERE OrderDate Between GetDate()-60 AND GetDate()-10000

0
 
psilli1Author Commented:
The problem is the <> bit, the date is working fine now.

I'll give what you said a go.

Thanks
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now