I am using an advanced sql query with the STRING_AGG function in ODC. But now I want to subtract some days to a date value to get the date of the first day of a month (01-01-2023).
I have tried the functions DATEADD, DATE_ADD and ADDDATE with (DAY, 5, date value) or (date value, INTERVAL 5 DAY) or (date value, 5), but nothing is working when I test the sql query.
Can someone help me to get the right Aurora PostgreSQL function and documentation. I am now using the following documentation: https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-mysql-migration-playbook/chap-sql-server-aurora-mysql.tsql.datetime.html.
Kind regards
Jan
PS: Is there already a SQLSandbox for ODC.
you are pointing to a MySql page, look at these instead :
https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.datetime.html
or
https://www.postgresql.org/docs/13/functions-datetime.html
judging by what I read there , I think simply doing datecolumn - 12 should allow you to substract 12 days of a date.
Don't know how you are using this to get the frist day of the month, though.
I never done anything with PostgreSQL, but i would imagine something like
select cast(date_trunc('month', <datecolumn>) as date) would do that.
Dorine
Hi Jan,
What is the result of DATEADD (DAY, 5, date value)? If you need to subtract you need to use negative days like -5.
I get the following errors:
An update,
You could try this, it works for me on my local Postgres instance
SELECT datetest, datetest - 5, datetest - interval '5 day'FROM "TEst"
See the official Postgres documentation https://www.postgresql.org/docs/current/functions-datetime.htmlSee Table 9.32. Date/Time Operators
Hi,
Relevant official ODC documention:
https://success.outsystems.com/documentation/outsystems_developer_cloud/onboarding_for_outsystems_developers/sql_queries_compared_to_outsystems_11/
Regards,
Daniel