Problems with MySQL BETWEEN clause!

why isnt this working?

[dreamcatcher]

Hi Guys,

I am writing a link tracking system which allows you to move group links from one category to another. I also want an option so you can move links between certain dates only.

So, I have a DATE field in my database, and three links, each of which have the following dates stored:

2004-07-27
2004-07-28
2004-07-29

ie, link 1 = 27, link 2 = 28 etc

Now for example, I want to move links only between the above dates. This is my sql syntax:

UPDATE ml_track SET catid = '2' WHERE catid = '4' AND linkdate BETWEEN 2004-07-27 AND 2004-07-29

Unfortunately nothing is happening. The three links are each in catid 4 and it should be changed to catid 2, but it remains as 4.

I`m assuming the between clause is somehow wrong? If I remove this, the catid field gets updated ok.

Any ideas?

Many thanks. :)

[Sparrow Nine]

Have you tried adding single quotes around each date?

[dreamcatcher]

Sparrow Nine, thank you for the help. Works like a charm :)

[coopster]

Welcome to WebmasterWorld, Sparrow Nine!

Right on, use quotation marks. Processing the statement as is, MySQL will subtract those figures.

SELECT 2004-07-27 AS Date; 
+------+ 
¦ Date ¦ 
+------+ 
¦ 1970 ¦ 
+------+

SELECT 2004-07-29 AS Date; 
+------+ 
¦ Date ¦ 
+------+ 
¦ 1968 ¦ 
+------+

[dreamcatcher]

Thanks coop. I never gave a thought to the apostrophes. Damn. And I looked at the code SO many times. Maybe its time I made an appointment at Specsavers. :)