Advanced query that shows records for the last 30 days

Advanced query that shows records for the last 30 days

  

My database is MSSQL.

I'm trying to get an advanced query working that only includes records for the last 30 days. I'm struggling however working with the OutSystems way to store dates.

If I look at the code that an aggregate produces however there is a variable @qepCurrdate in there:


SELECT ENTMP_Application.[DATE]
FROM [OSDEV1].DBO.[OSUSR_TYN_TMP_APPLICATION] ENTMP_Application
WHERE (ENTMP_Application.[DATE] > (convert(datetime, convert(varchar(10), (dateadd(day, (-30), (convert(datetime, substring(@qepCurrdate, 1, 10), 120)))), 120))))


How would I make current code work in an advanced query?

I tried 

SELECT ENTMP_Application.[DATE]
FROM [OSDEV1].DBO.[OSUSR_TYN_TMP_APPLICATION] ENTMP_Application
WHERE (ENTMP_Application.[DATE] > (convert(datetime, convert(varchar(10), (dateadd(day, (-30), (convert(datetime, substring(CONVERT (date, GETDATE()), 1, 10), 120)))), 120))))

but that gives an error :argument date type date is invalid for argument 1 of substring function.

I also tried
WHERE  [OSDEV1].DBO.[OSUSR_TYN_TMP_APPLICATION].[DATE] > DATEADD(day, -10, CONVERT(date, [OSDEV1].DBO.[OSUSR_TYN_TMP_APPLICATION].[DATE]))

But that doesn't give a proper response.

Anyone can explain how to get the records from the last 30 days in an advanced query?

(above examples have been simplified for easier answer, real advanced query will have complex select part)

Solution

Hello Paul...

Why not just pass an input parameter (TresholdDate) with the treshold date, where the value passed to the SQL will be (assuming it is a DATE and not a DATETIME)? 

DateTimeToDate(AddDays(CurrDate(), -30))

And in your query you simply do:

WHERE (ENTMP_Application.[DATE] > @TresholdDate

This does not work?

Cheers.

Solution

I have created below filters & it is working perfect :)

Notifications is my Local Table:


Today:
Notifications.notification_date = FormatDateTime(CurrDate(),"yyyy-MM-dd")


Last Month:
Notifications.notification_date >=
NewDate(
If(Month(CurrDate()) =1,Year(CurrDate())-1,Year(CurrDate())),
If(Month(CurrDate())-1 > 0,Month(CurrDate())-1,12),
1)
and
Notifications.notification_date < NewDate(Year(CurrDate()),Month(CurrDate()),1)


LAST WEEK:
Notifications.notification_date >= NewDate(Year(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Month(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Day(AddDays(CurrDate(),-DayOfWeek(CurrDate()))))



Thanks,

Assif

Thank you for helping Eduardo.
This indeed is also a good solution to only get a certain time period.

assif_tiger wrote:


I have created below filters & it is working perfect :)

Notifications is my Local Table:


Today:
Notifications.notification_date = FormatDateTime(CurrDate(),"yyyy-MM-dd")


Last Month:
Notifications.notification_date >=
NewDate(
If(Month(CurrDate()) =1,Year(CurrDate())-1,Year(CurrDate())),
If(Month(CurrDate())-1 > 0,Month(CurrDate())-1,12),
1)
and
Notifications.notification_date < NewDate(Year(CurrDate()),Month(CurrDate()),1)


LAST WEEK:
Notifications.notification_date >= NewDate(Year(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Month(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Day(AddDays(CurrDate(),-DayOfWeek(CurrDate()))))



Thanks,

Assif


I'm afraid I get an error saying "Incorrect syntex near the keyword IF. CurrDate is not a recognized built-in function name.
Are you sure this is MSSQL code (and not MySQL)?

(My problem right now is solved though, by using the above suggestion.)


Paul. wrote:

assif_tiger wrote:


I have created below filters & it is working perfect :)

Notifications is my Local Table:


Today:
Notifications.notification_date = FormatDateTime(CurrDate(),"yyyy-MM-dd")


Last Month:
Notifications.notification_date >=
NewDate(
If(Month(CurrDate()) =1,Year(CurrDate())-1,Year(CurrDate())),
If(Month(CurrDate())-1 > 0,Month(CurrDate())-1,12),
1)
and
Notifications.notification_date < NewDate(Year(CurrDate()),Month(CurrDate()),1)


LAST WEEK:
Notifications.notification_date >= NewDate(Year(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Month(AddDays(CurrDate(),-DayOfWeek(CurrDate()))),Day(AddDays(CurrDate(),-DayOfWeek(CurrDate()))))



Thanks,

Assif


I'm afraid I get an error saying "Incorrect syntex near the keyword IF. CurrDate is not a recognized built-in function name.
Are you sure this is MSSQL code (and not MySQL)?

(My problem right now is solved though, by using the above suggestion.)


Hi Paul,
It's a kind of expression I used under Filter with my local Entity Aggregate.