Hello everyone,
I’m currently working on generating data for a column chart to display the number of (dispatch)requests for a particular date range (in ODC).The idea is to create a date list and count the number of requests submitted (with DispatchRequest.SubmittedOn, which is a DateTime) for each date.
However, I’m running into date casting problems when testing my Advanced SQL query. Specifically, I get the following error:Unable to execute query. Invalid cast from 'DateTime' to 'Int32'.I’ve tried various approaches to cast DateTime to Date, such as using CAST and the ::date notation, but it seems like I can’t get the correct syntax to work.Here’s the query I’m trying to execute:
WITH RECURSIVE DateRange AS (
SELECT
CAST(@StartDate AS DATE) AS DateValue -- Start of the date range
UNION ALL
CAST(DateValue + INTERVAL '1 day' AS DATE) -- Increment the date by 1 day
FROM
DateRange
WHERE
DateValue < @CurrDate -- Generate dates up to the current date
)
dr.DateValue AS request_date,
COUNT({DispatchRequest}.[Id]) AS request_count
DateRange dr
LEFT JOIN
{DispatchRequest}
ON {DispatchRequest}.[SubmittedOn]::date = dr.DateValue
GROUP BY
dr.DateValue
ORDER BY
dr.DateValue;
Has anyone encountered a similar issue? What would be the correct way to handle date casting in OutSystems SQL? Any help or pointers would be greatly appreciated!
Thank you in advance!
I received feedback that using the generate_series function works for creating a date range, and I wanted to share the working solution for others who might encounter the same issue. Here's the query that worked for me:
WITH DateRange AS (
DateValue
generate_series(
@StartDate::date, -- Starting date for the series
@EndDate::date, -- Ending date for the series
'1 Day' -- Step interval of 1 day
) AS DateValue
dr.DateValue AS request_date, -- The date from the generated range
COUNT({DispatchRequest}.[Id]) AS request_count -- Count of matching records in DispatchRequest
{DispatchRequest} -- DispatchRequest table
ON CAST({DispatchRequest}.[ValidationsCheckedOn] AS DATE) = dr.DateValue
dr.DateValue -- Group results by the generated dates
dr.DateValue; -- Sort results in chronological order
Hi @Fieke Thijssen ,
I have updated the query, could you please try it.
SELECT CAST(@StartDate AS DATE) AS DateValue -- Start of the date range
SELECT DateValue + INTERVAL '1 day' -- Increment the date by 1 day
FROM DateRange
WHERE DateValue < CAST(@CurrDate AS DATE) -- Generate dates up to the current date
ON CAST({DispatchRequest}.[SubmittedOn] AS DATE) = dr.DateValue
Thanks,
Vipin Yadav
Hi @Vipin Yadav
Thanks for you quick reply. I've tried casting the StartDate and CurrDate as well, however this resulted in the error below
Test query failed with the following error: Unable to execute query. ERROR: recursive query "daterange" column 1 has type date in non-recursive term but type timestamp without time zone overall Hint: Cast the output of the non-recursive term to the correct type. Position: 45
To get rid of this error I adjusted my query as presented below.
SELECT CAST(DateValue + INTERVAL '1 day' AS DATE) -- Increment the date by 1 day
WHERE DateValue <= CAST(@CurrDate AS DATE) -- Generate dates up to the current date)
FROM DateRange dr
GROUP BY dr.DateValue
ORDER BY dr.DateValue;
However, this brings me back to my original error:
Test query failed with the following error: Unable to execute query. Invalid cast from 'DateTime' to 'Int32'.
Hi,
Can you check with below SQL. Added the DATEADD sql function for incrementing the date.
;WITH DateRange AS (
--SELECT DateValue + INTERVAL '1 day' -- Increment the date by 1 day
SELECT DATEADD(day,1,DateValue)
Thanks.
Hi @BHUPENDRA SINGH
Unfortunately it seems like PostgreSQL does not provide the DATEADD function.
Therefore I used the SELECT CAST(DateValue + INTERVAL '1 day' AS DATE) function