I need help with a sql server stored procedure

Hi all,
I need help using GETDATE() in my stored procedure.  When i used GETDATE in the stored procedure using the query below, it returns nothing.  how can I fix this?

      SELECT OrderNumberCustomer, TrackingNumber, PackedDateTime
      FROM  ShippingPackage
      WHERE (OrderNumberCustomer LIKE + @CustNumber + '%')
      AND PackedDateTime = GetDate()  -3

Thank in advance,
mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
try this instead...
SELECT OrderNumberCustomer, TrackingNumber, PackedDateTime
      FROM  ShippingPackage
      WHERE (OrderNumberCustomer LIKE + @CustNumber + '%') 
      AND PackedDateTime = convert( datetime,  convert(   varchar(10), GetDate() , 112) , 112  )  -3

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can try this

SELECT OrderNumberCustomer, TrackingNumber, PackedDateTime
      FROM  ShippingPackage
      WHERE (OrderNumberCustomer LIKE + @CustNumber + '%')
      AND COVERT(DATE,PackedDateTime) = DATEADD(D,-3,CONVERT(DATE,GetDate()))

Open in new window

0
Dung DinhDBA and Business Intelligence DeveloperCommented:
Understood that you want to query orders, which were packaged 3 days ago. And I also assume that PackedDateTime  contains time such as 2015-10-07 15:02:00

DECLARE @FromDate datetime = CONVERT(datetime,CONVERT(varchar(10),GETDATE(),112))-3
,@ToDate datetime =CONVERT(datetime,CONVERT(varchar(10),GETDATE(),112))-2

SELECT OrderNumberCustomer, TrackingNumber, PackedDateTime
FROM  ShippingPackage
WHERE (OrderNumberCustomer LIKE  @CustNumber + '%') 
      AND PackedDateTime  >= @FromDate AND PackedDateTime  < @ToDate

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dung DinhDBA and Business Intelligence DeveloperCommented:
@Vikas Garg: In my experience, if we have an index on PackedDateTime column, we should avoid converting the column in WHERE clause because SQL Server engine can use index-scan operator instead of index-seek. Then we will encounter performance issue if we work with a large table.

Thanks,
0
PortletPaulfreelancerCommented:
GETDATE() provides both date the TIME which is precise to approx. 3 milliseconds

so if you try to get something EQUAL to GETDATE() - 3 you have to have rows with that very precise date AND TIME and because you probably don't have such rows you are getting a NULL result.

So, your SP is getting getdate() OK, you just need to modify the precision to suit your data, which is why you have a variety of choices shown above, but:

Please don't convert every row of data.

These are the 2 most efficient ways to convert the precision of getdate() to assist you:

1. DATEADD(day, DATEDIFF(day,0, GETDATE() ), -3) -- all versions of mssql, resuts in a datetime value with time at 00:00:00

2. DATEADD(day, -3, CAST(GETDATE() as DATE) ) -- from mssql 2012 onward, results in date (without time)

E.G.

SELECT
      OrderNumberCustomer
    , TrackingNumber
    , PackedDateTime
FROM ShippingPackage
WHERE (OrderNumberCustomer LIKE +@CustNumber + '%')
      AND PackedDateTime = DATEADD(day, DATEDIFF(day,0, GETDATE() ), -3)
0
Umair QamarCommented:
DATEADD(dd, DATEDIFF(day,0, GETDATE() ), -3)
for reference on -3 you can view this link. [SQL Server 2008]

1: http://www.w3schools.com/sql/sql_dates.asp
0
PortletPaulfreelancerCommented:
the -3 isn't a mystery

there are 2 functions

DATEDIFF(day,0, GETDATE() )

this returns the number of days from the "zero date" (which is 1900-01-01 in T-SQL) until today

The next function is

DATEADD(dd,      ........       , -3)

so now we add a number of days (which is the result of the first function) to the "minus 3 date"
select
  dateadd(day,0,0)
, dateadd(day,-3,0)
, dateadd(day,0,-3)
, dateadd(dd,-3,0)
, dateadd(dd,0,-3)

, datediff(day,0,getdate())

, dateadd(day, 42284, 0)


RESULT:
|------------------|-------------------|-------------------|-------------------|-------------------|-------|------------------|
| January, 01 1900 | December, 29 1899 | December, 29 1899 | December, 29 1899 | December, 29 1899 | 42284 | October, 09 2015 |

Open in new window

0
Scott PletcherSenior DBACommented:
Paul's got the critical parts of the answer.

But, one minor correction, ">=" instead of "=", and one clarification, "- 3, 0" instead of ", -3".  But pls, NO pts for me.

 AND PackedDateTime >= DATEADD(day, DATEDIFF(day,0, GETDATE() ) - 3, 0)

The standard calc to strip time from the current date is:
DATEADD(day, DATEDIFF(DAY, 0, GETDATE()), 0)

It is much more logically consistent to subtract 3 days in the middle operator than at the end.
0
mainrotorAuthor Commented:
Thank you all.  I will try your suggestions and get back to you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.