Select Statement that needs to use current time

[rickjamesb]

I need to search for ProductIDs and UnitPrices for products that have not been sold within the past six months. The six months is determined by the date the Stored Procedure is executed.

So i guess i would just need to find the current server time, do some type of interval of 6 months, and use a NOT IN statement?

How do i find the current server time? Is my WHERE statement anywhere close to being correct? I have no experience with working with dates in SQL - looking for a good online tutorial.

SELECT ProductID, UnitPrice
FROM Products
WHERE ProductID NOT IN
     (SELECT ProductID
      FROM Products
      WHERE DateDiff(Month, 'CURRENT TIME', OrderDate > 6)

 

[gmmastros]

SELECT ProductID, UnitPrice
FROM Products
WHERE ProductID NOT IN
     (SELECT ProductID
      FROM Products
      WHERE DateDiff(Month, [!]OrderDate, GetDate()[/!]) > 6

For a good explanation of dates, look at: thread183-1240616 and thread183-1248617

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Thanks George, i appreciate your help! As a beginner, i'm starting to really enjoy SQL. Its not easy, but i can almost understand most of the basics now - especially with help like your and others here!

 

[gmmastros]

There is more date stuff, too. You should look at the faq's

http://tek-tips.com/faq.cfm?pid=183

I especially like the one from vongrunt. It's very informative.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '6'.

I then tried:
WHERE DateDiff(Month, OrderDate, GetDate() > 6)

Which gives:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '>'.

I'll try a few other ways.

 

[gmmastros]

Sorry. It's easy to miss parenthesis.

SELECT ProductID, UnitPrice
FROM Products
WHERE ProductID NOT IN
     [!]([/!]SELECT ProductID
      FROM Products
      WHERE DateDiff(Month, OrderDate, GetDate()) > 6 [!])[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rickjamesb]

Thanks...

That last link you provided looks real good!

 

[gmmastros]

If you want to have more fun...

Click this link which will open up another instance of internet explorer:

http://tek-tips.com/threadminder.cfm?pid=183

You will, of course, be looking at tek-tips.

There is a section of the web page that has a menu bar with, FORUM, SEARCH, FAQs, Links, Jobs and Whitepapters.

Just below Whitepapers, you will see a section for 'thread order'

The default thread order is order by date. A couple icons to the right will be a purple star. Purple stars are 'awarded' to people when they post a helpful message in a thread. By clicking on the purple star in the 'thread order' section, the threads will be sorted be most helpful.

There's likely to be stuff in there that you never had a clue that SQL Server could do. Just remember to change the thread order back to 'by date' when you are done reading.

Since you are also working with VB, I suggest you do the same thing in the VB forum. forum222 Even if you know VB really well, I bet you will still learn a thing or 2.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom