Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Obtaining the date in a Stored Proc

Posted on 2001-08-01
7
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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