[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Obtaining the date in a Stored Proc

Posted on 2001-08-01
7
Medium Priority
?
204 Views
Last Modified: 2012-08-14
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
Comment
Question by:psilli1
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:acampoma
ID: 6341868
is orderdata a datetime field??
0
 
LVL 6

Expert Comment

by:acampoma
ID: 6341934
is orderdata a datetime field??
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 6342434
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 1

Author Comment

by:psilli1
ID: 6343786
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
 
LVL 1

Author Comment

by:psilli1
ID: 6344173
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
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 120 total points
ID: 6345368
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
 
LVL 1

Author Comment

by:psilli1
ID: 6347830
The problem is the <> bit, the date is working fine now.

I'll give what you said a go.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

640 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