Calculate days since created table transformer

[Leona Vo]

Hi,

In my Table Transformer, I would like to calculate the number of days since Created.

The following select statement produces what you see in the attached.

SELECT FORMATDATE(NOW()), FORMATDATE(T1.'Created') FROM T*

However, the below produces nothing. What am I missing?

SELECT FORMATDATE(NOW()) - FORMATDATE(T1.'Created') FROM T*

Thank you.

UPDATE: From one of the online post, I was able to find the answer.

(CAST(ROUND(ceiling(DATEDIFF(DAY,T1.'Created',NOW())))as int))

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Leona Vo ,

You may also try the following SQL query:

SELECT *,
DATEDIFF(day,T1.'Date',"today")
AS 'Date Diff'
FROM T*

The date format for this example in the Table Transformer macro settings (Settings tab -> Date format) should be set as "m/d/y".

Hope this also helps.

[Leona Vo]

Hi @Katerina Kovriga {Stiltsoft} thank you for your reply. Your solution is much easier to read and understand. It works beautifully. I add ROUND() to the function to produce integer.

SELECT T1.'Created', ROUND(DATEDIFF(day, T1.'Created', "today")) as 'Ticket Age' FROM T*

[arijit chatterjee]

Hi,

Thanks for your post. I also had a similar issue previously but right now i have got the ticket age from your query but next is that i also need that out of the total days say 100 there will be Saturday and Sunday as well , how to reflect correct number of days in Integer excluding Weekends. Any help here will be much appreciated!!

Thanks

Arijit

[Stiltsoft support]

Hi @arijit chatterjee ,

Please check this part of our documentation: https://docs.stiltsoft.com/tfac/dc-server/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Calculatingthenumberofworkdaysinaperiodoftime

[Leona Vo]

(CAST(ROUND(ceiling(DATEDIFF(DAY,T1.'Created',NOW())))as int))