I am working on a custom table, so I need to calculate my due date which needs to be added from a SLA value. However, I need to make sure that the SLA is added only on working days, which seems impossible to implement in ODC using advance SQL. Is there any way or built in function to do this or is there a function to find the day of week ?What I have tried:Used DAYOFWEEK(), WEEKDAY(), EXTRACT(DOW AS DATE date), DATEADD(), DATE_FORMAT()Source: https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.datetime.html
Hi @Faizan Ul Haq,
In ODC and OS11 both having this built in function to get Day of week.
Hope this will help you!!
Regards,
Rajat
I am using SQL and the data I need to operate is in those inner queries so I only can use these functions,
SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName;
this one for day name like Wednesday
SELECT DATEPART(WEEKDAY, GETDATE()) AS DayNumber;
this one is used for day number like 4.
Both did not work for me
work day is base on carlendar but not by week , i think u shoud define which day is for work. so no function can solute it.
Try this Sql
WITH DateRange AS (
SELECT @StartDate AS Date UNION ALL SELECT DATEADD(DAY, 1, Date) FROM DateRange WHERE DATEADD(DAY, 1, Date) <= @EndDate)SELECT COUNT(*) AS BusinessDaysFROM DateRangeWHERE DATENAME(WEEKDAY, Date) NOT IN ('Saturday', 'Sunday')OPTION (MAXRECURSION 0);
This sql will work and Saturday and Sunday in the date range.
If you have a company calender with special hoildays record then left join with DateRange Example:
Left join {CompanyHolidays} on {DateRange}.[Date] = {CompanyHolidays} .[Date]
After in where clause add extra Filter
WHERE DATENAME(WEEKDAY, Date) NOT IN ('Saturday', 'Sunday') and {CompanyHolidays}.[IsHoilday] = 0
Change The SQL syntax to PostgreSQL because ODC use the Postgre.
Follow this link: https://success.outsystems.com/documentation/outsystems_developer_cloud/onboarding_developers/sql_queries_compared_to_outsystems_11/
I could have share the SQl in Postgre, but at present i don't have access to ODC which i couldn't test it.
I tried this DATEADD(DAY, 1, Date) and other postgre syntax but it didnt work, seems like only a few functionalities are enabled, I am not even sure if it could be possible.
In Postgre format.!
SELECT
generate_series('1999-03-27'::DATE, '1999-04-29'::DATE, '1 day') AS Date,
TRIM(TO_CHAR(generate_series('1999-03-27'::DATE, '1999-04-29'::DATE, '1 day'), 'Day')) AS day_of_week
)
SELECT Date, day_of_week
FROM DateRange
WHERE day_of_week NOT IN ('Saturday', 'Sunday');
try this
Just to expand this question a bit beyond finding a day of week function in SQL :
I don't think this will really solve your problem of counting SLA days, surely, you also don't want to count certain holidays that don't fall in the weekend ?
Also, to expand the possible solution a little bit : do you need this calculation to be applied in the SQL, are you for example going to filter or sort in your SQL on the resulting date (i.e. sort by due date ascending) If not, you could just calculate the due date after the query.
And yet another way to open up the possible solutions, why not do the calculations in the CreateOrUpdate wrapper, and store the due dat in the table ? That will make for faster retrieval, and you can just use OutSystems logic any way you want to do it.
Dorine