I want to get diffday from two date column but i Couldn't do that in SQL Widgets. Is there way to do?
I want to caluclate diffdays from two date using sql Widgets. Currently I am using SQL Function DateDIFF(dd,column1date,Column2Date).
In advanced SQL you can use this function, if you use the right format parameters. Dsys is not dd but dy.
See this link for more info on the function: https://www.w3schools.com/sql/func_sqlserver_datediff.asp
And refer to the SQL Server documentation if you nead more detail.
Example from my environment :
Hi I am getting error Invalid identifier DATEDIFF in SQL Widget
Hi Githiyon,
What database are you using, Microsoft SQL Server (MSSQL) or Oracle? DATEDIFF is an MSSQL function, it won't work in Oracle. For Oracle it seems you can just subtract dates to get the number of days.
Hi I am joining two table one is outsystem table another is oracle table but i am calculating date diffrence is Outsystem table only.
OutSystems can use both Oracle and MSSQL as its primary database. If you can join an external Oracle table to an OutSystems table, that means that both tables are on the same database, so you are using Oracle for OutSystems as well. Therefore try to subtract the two dates to get the difference in days.
Please try something like this if you using oracle.
SELECT
{Customer} .[Id],
{Customer} .[Name],
{ORDERS}.[ORDERDATE],
{ORDERS}.[SHIPDATE],
(ORDERS}.[SHIPDATE] - {ORDERS}.[ORDERDATE])
FROM {ORDERS}
Hi I have used for Trunc builtin function in my Sql Widget problem is solved.
EX: trunc(sysdate)-trunc(mydate) solved.