hi i want to run a query that uses substr and to_date
but when I try, there is an error in the substr and to_date sections
so I want to ask how to write queries on sql outsystem for substr and to_date.
Thank You
Hi Sherly,
Thank you for your question. A little more information will help. A screenshot of your code and an explanation of the datatypes should do it.
It depends on whether you are using an aggregate or advanced SQL. Which are you using?
An aggregate can use a subset of OutSystems builtin functions and will translate them appropriately for your database.
An advanced SQL query must use the syntax of your specific database type. What type of database are you using? eg SQL Server or Oracle, or MySQL? Substring has a different syntax in advanced SQL than in OutSystems code.
SQL Server SUBSTRING() Function (w3schools.com)
Also what type of variable are you attempting to convert to a date? Generally you need to tell an advanced SQL what format to convert it to.
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#i-convert-date-and-time-data-types
I hope this helps!
Kind regards,
Stuart
Hi Stuart Harrisi tried with to_date, and i got an error like picture twoand I have tried using to_char, but I got an error when testing the SQL query the first image
so what to do?Thank You,
Try to use CAST() function instead.