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,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Vikas GargAssociate Principal EngineerCommented:

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

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

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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.

PortletPaulEE Topic AdvisorCommented:
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)


    , TrackingNumber
    , PackedDateTime
FROM ShippingPackage
WHERE (OrderNumberCustomer LIKE +@CustNumber + '%')
      AND PackedDateTime = DATEADD(day, DATEDIFF(day,0, GETDATE() ), -3)
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
PortletPaulEE Topic AdvisorCommented:
the -3 isn't a mystery

there are 2 functions


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"
, dateadd(day,-3,0)
, dateadd(day,0,-3)
, dateadd(dd,-3,0)
, dateadd(dd,0,-3)

, datediff(day,0,getdate())

, dateadd(day, 42284, 0)

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

Open in new window

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:

It is much more logically consistent to subtract 3 days in the middle operator than at the end.
mainrotorAuthor Commented:
Thank you all.  I will try your suggestions and get back to you.
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.