Date to select 30 months back

1

In the following SQL for SQL 12.00.6433
e.actual_date is a datetime Null column. The below is giving an error: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'CURRENT_DATE'.
The purpose here is that we want to see all clients activity only for the past 2 and half years from the current date.

select distinct ac.people_id, ac.first_name, ac.last_name, ac.id_no, ad.city, ed.is_service_event, co.description as County,
pe.program_name, pe.program_start_date, pe.program_end_date, el.actual_date, el.approved_date, el.program_providing_service, el.duration, ed.event_name
from all_clients_view ac
inner join address ad on ad.people_id = ac.people_id
left join county co
on ad.county = co.county_id
inner join program_enrollment_expanded_view pe
on pe.people_id = ac.people_id
inner join event_log el
on ac.people_id = el.people_id
inner join event_definition ed
on el.event_definition_id = ed.event_definition_id
where el.actual_date > CURRENT_DATE(); - (365 * 2 + 366 / 2) )

2

You probably don't want a semicolon in the last line.

1 Like
3

either way with or without it, it is not working
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'CURRENT_DATE'.

4

I think i have to use getdate. although timestamp works on my machine.

5

I'm thinking that code isn't what you want to use. Try the following... it auto-magically handles differences in the length of months and leap years.

WHERE el.actual_date >= DATEADD(mm,-30,GETDATE())

1 Like
6

Be careful here - using DATEADD with GETDATE() returns a date/time at the time of execution. If you really want 30 months ago to include the full day 30 months back, then you would want:

WHERE el.actual_date >= DATEADD(mm,-30,CAST(GETDATE() AS date))

And if you actually want the full month 30 months in the past:

WHERE el.actual_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 30, 0)
2 Likes
7

That would be true. Thanks for the catch, Jeffrey.

1 Like