Using SQL CURDATE() Interval

[davidjennings]

Hi all - use the curdate() or current date to look at newly added awards in the last 3 months
The SQL table used to store the created date when an award was added is tbl_net_centre.reg_date and keeps throwing up an error on the <= If anyone can advise the correct syntax for this.

-- New Networks by date less than 3 months--
select 
    tbl_centre.centre_id,
    tbl_centre.centre_no,
    tbl_centre.centre_name,
    tbl_region.region,
    tbl_net.network_code,
    tbl_centre.reg_date,
    tbl_net_centre.reg_date,
    count(tbl_centre.reg_date) as Nunmber_of_New_networks 

FROM
    tbl_centre
     LEFT JOIN
    tbl_net_centre ON tbl_centre.reg_date = tbl_net_centre.reg_date
        LEFT JOIN
    tbl_net_centre ON tbl_centre.centre_id = tbl_net_centre.centre_id
        LEFT JOIN
    tbl_net ON tbl_net_centre.ntwrk_cd = tbl_net.network_code
        LEFT JOIN
    tbl_region ON tbl_centre.region_code = tbl_region.region_ID
WHERE
    tbl_centre.reg_status = 'R'
        AND tbl_net_centre.net_reg_stts in ('R' , 'P')
        AND tbl_region.region_ID = '3'
       AND 'tbl_net_centre.reg-date' <=(CURDATE(), INTERVAL 3 MONTH)
group by tbl_centre.centre_id
having Nunmber_of_New_networks =1 AND network_code='E'
limit 1000;

Thanks in advance

D

[rch1231]

Hello,

I think you are missing the DATE_ADD and have the wrong quote marks around the field name. Try something like this from the mysql manual:

SELECT something 
FROM tbl_name
WHERE `tbl_net_centre.reg-date` <= DATE_ADD(CURDATE(),INTERVAL 3 MONTH) ;

[imBaCodes]

And rch1231 is correct . you better try it out.
It would be better to use NOW()..

[davidjennings]

Hi Both thanks for your comments. Can you explain why you NOW() would be better, I know you can use that on insert to db. Can you give an example

Thanks
d

[imBaCodes]

Its better to use NOW() because it returns the date and time portions as a timestamp in various formats, depending on how you requested it. NOW()
While on the other hand CURDATE() retruns only a portion of the current date. Its a big advantage using NOW() .
CURDATE()

[davidjennings]

Thanks