We're attempting to output some values to email using the set variable obj, but the format is not holding. Is there a method for formatting output within TDP when it doesn't pick up the format specified in a query?
SELECT
Cast(Min(last_login_dt ) AS DATE) AS MinLoginDt
,Cast(Max(last_login_dt) AS DATE) AS MaxLoginDt
,Cast(Min(created_dt) AS DATE) AS MinCreatedDt
,Cast(Max(created_dt) AS DATE) AS MaxCreatedDt
,Cast(Min(registration_dt) AS DATE) AS MinRegDt
,Cast(Max(registration_dt) AS DATE) AS MaxRegDt
,Cast(Min(user_created_dt) AS DATE) AS MinUserCreateDt
,Cast(Max(user_created_dt) AS DATE) AS MaxUserCreateDt
,Cast(Min(user_activation_dt) AS DATE) AS MinUserActDt
,Cast(Max(user_activation_dt) AS DATE) AS MaxUserActDt
,Cast(Count(DISTINCT profile_id) AS Format 'G999999999D99') AS DistinctID
,Cast(Count(DISTINCT userid) AS Format 'G999999999D99') AS DistinctUser
FROM db.table_name
What we're looking for is commas for large ints and to remove the trailing timestamp since these are date values onlin.
This is what TDP is returning in the output. Why it returns a to_date function in the output is an old problem we've seen that was never resolved in v5.0.
Distinct ID: 1678616
Distinct User: 2737012
MinLoginDt: To_date('08/06/2016 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MaxLoginDt: To_date('08/06/2016 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MinCreatedDt: To_date('02/02/2005 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MaxCreatedDt: To_date('04/23/2020 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MinRegDt: To_date('02/02/2005 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MaxRegDt: To_date('04/23/2020 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MinUserCreateDt: To_date('08/05/2016 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MaxUserCreateDt: To_date('04/23/2020 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MinUserActDt: To_date('08/06/2016 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')
MaxUserActDt: To_date('04/23/2020 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')