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_ApplicationWHERE (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_ApplicationWHERE (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 triedWHERE [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)
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.
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)andNotifications.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
assif_tiger wrote:
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:
Hi Paul,It's a kind of expression I used under Filter with my local Entity Aggregate.
Thank you.
Thank you for helping Eduardo.This indeed is also a good solution to only get a certain time period.