Solved

Obtaining the date in a Stored Proc

Posted on 2001-08-01
7
191 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:bhess1
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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:
bhess1 earned 30 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now