1,105,534 Community Members

Using SQL CURDATE() Interval

Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
rch1231
Veteran Poster
1,187 posts since Sep 2009
Reputation Points: 131 [?]
Q&As Helped to Solve: 194 [?]
Skill Endorsements: 13 [?]
 
0
 

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) ;
Member Avatar
imBaCodes
Junior Poster
179 posts since Apr 2013
Reputation Points: 2 [?]
Q&As Helped to Solve: 20 [?]
Skill Endorsements: 9 [?]
 
1
 

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

Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
imBaCodes
Junior Poster
179 posts since Apr 2013
Reputation Points: 2 [?]
Q&As Helped to Solve: 20 [?]
Skill Endorsements: 9 [?]
 
1
 

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()

Member Avatar
davidjennings
Posting Whiz in Training
218 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks

Question Answered as of 11 Months Ago by imBaCodes and rch1231
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: