Help with Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Help with Code

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2008-10-14 : 08:31:49

SELECT paypal_payment_info.datecreation, paypal_cart_info.itemname, paypal_cart_info.itemnumber, paypal_cart_info.quantity
FROM paypal_payment_info INNER JOIN paypal_cart_info ON paypal_payment_info.txnid = paypal_cart_info.txnid
WHERE paypal_payment_info.datecreation=Date()+7;

I have tried the above sql code and it just isn't working and I am not sure what part of it is incorrect.

tina m miller

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:36:43

What is your objective? To get all records created exactly seven days in the future?
Or all records that are at most seven days old?
Beware of you have time information other than midnigh (00:00) in datecreation column/field you will have to use other logic.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 08:36:54

[code]SELECT paypal_payment_info.datecreation,
paypal_cart_info.itemname,
paypal_cart_info.itemnumber,
paypal_cart_info.quantity
FROM paypal_payment_info
INNER JOIN paypal_cart_info ON paypal_payment_info.txnid = paypal_cart_info.txnid
WHERE paypal_payment_info.datecreation >= Date() - 7[/code]

E 12°55'05.63"
N 56°04'39.26"

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2008-10-14 : 09:18:50

I don't have a time stamp in there just date. And just trying to get the orders placed as of today and then +7 days into the future. When I run the query and replace just the = where the date is for >= it still doesn't run. It doesn't like the () information. It says error on that line right syntax to use near ) + 7

So for some reason I don't think it likes the () but not sure what is to go there.

tina m miller

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:22:31

E 12°55'05.63"
N 56°04'39.26"

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2008-10-14 : 09:38:05

It doesn't like getdate() either. I tried taking and running the entire code through Access using the SQL union and it doesn't like it so I went back to this original date() and Access SQL likes that. But when I put it in mysql that is through phpmyadmin it responds with this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') - 7

tina m miller

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2008-10-14 : 09:47:44

Got it. How wierd this mysql 4.1 version is. It only works with:

SELECT paypal_payment_info.datecreation, paypal_cart_info.itemname, paypal_cart_info.itemnumber, paypal_cart_info.quantity
FROM paypal_payment_info
INNER JOIN paypal_cart_info ON paypal_payment_info.txnid = paypal_cart_info.txnid
WHERE paypal_payment_info.datecreation = now( ) -7

Or if I do >= or <= whatever I put it has to be now(). Thanks for all your help on this.

tina m miller

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 09:57:01

I think CURRENT_TIMESTAMP is used in MySQL too.

E 12°55'05.63"
N 56°04'39.26"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-10-17 : 07:32:47

part of the confusion in the answers given arose....from bit you finally revealed...."MySQL..."

if you read the FAQ's....we're 99.999% a MS SQL server site....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources