Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 4 of 4 total
Thread INTERVAL Issue/Question
Thu, Mar 25 2021 8:31 PMPermanent Link

Ian Branch

Avatar

Hi Team,
Given the following SQL..
{sql}
select *, cast(Cast(ReportLastSent - Date '2021-01-01' as interval day) as Integer) as tDays from ScheduledReports
{sql}
ReportLastSent is a TimeStamp field with 2021-02-03 00:00 in it.
The record returns OK but I get no tDays value. Frown
What have I missed please?

Ultimately, this part "cast(Cast(ReportLastSent - Date '2021-01-01' as interval day) as Integer) as tDays" represents the Day of the Year.

Regards & TIA,
Ian
Fri, Mar 26 2021 12:00 AMPermanent Link

Raul

Team Elevate Team Elevate

On 3/25/2021 8:31 PM, Ian Branch wrote:
> Hi Team,
> Given the following SQL..
> {sql}
> select *, cast(Cast(ReportLastSent - Date '2021-01-01' as interval day) as Integer) as tDays from ScheduledReports
> {sql}
> ReportLastSent is a TimeStamp field with 2021-02-03 00:00 in it.
> The record returns OK but I get no tDays value. Frown
> What have I missed please?
>
> Ultimately, this part "cast(Cast(ReportLastSent - Date '2021-01-01' as interval day) as Integer) as tDays" represents the Day of the Year.
>
> Regards & TIA,
> Ian
>

Ian,

How about

select *,cast(cast(ReportLastSent as Date) - Date '2021-01-01' as
interval day) as tDays from ScheduledReports

Raul
Fri, Mar 26 2021 1:19 AMPermanent Link

Ian Branch

Avatar

Ahhh.  Tks Raul,  I was thinking back to front.
Obvious now I see it.

Regards,
Ian
Fri, Mar 26 2021 5:36 PMPermanent Link

Ian Branch

Avatar

So.  FWIW, turns out this will do the same thing..
{sql}
select *, extract(DayofYear from ReportLastSent) as tDays from ScheduledReports
{sql}

Ian
Image