Solved

Obtaining the date in a Stored Proc

Posted on 2001-08-01
7
192 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

26 Experts available now in Live!

Get 1:1 Help Now