NOTICE: Branded Content
NOTICE: Certain versions of content (“Material”) accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.
Quality Center / ALM User Discussions
cancel

SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

SOLVED
Go to solution
HellsBells Absent Member.
Absent Member.

SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

I'm extracting various fields using SQL in an Excel Report in the Dashboard / Analysis

On of those fields is TC_Exec_Date but it extracts as yyyy-mm-dd 00:00:00 but I would like it as dd/mm/yyyy

Not sure how to do this - whether in the SQL or the after post-processing

I've never altered anything in the post-processing

 

Please advise / give me example code of how to do this - all/any suggestions gratefully received - thanks.

  

Select
    CF.CF_ITEM_NAME AS "TesLab_Folder_Name",
    T.TS_NAME AS "Test_Name",
    TI.TC_EXEC_DATE AS "Exec_Date",
    TI.TC_STATUS AS "Exec_Status",
    TI.TC_USER_TEMPLATE_04 as "Previous Exec Status",
    TI.TC_USER_TEMPLATE_02 as "Previous Exec Date",
    TI.TC_USER_TEMPLATE_03 as "Previous Exec Time"

 From
     Test as "T"

   JOIN TESTCYCL "TI" ON "TI"."TC_TEST_ID" = "T"."TS_TEST_ID"
   LEFT JOIN CYCLE "CYCL"  ON "CYCL"."CY_CYCLE_ID" = "TI"."TC_CYCLE_ID"
   LEFT JOIN CYCL_FOLD "CF" ON "CF_ITEM_ID" = "CYCL"."CY_FOLDER_ID"
   ORDER BY  CYCL.CY_CYCLE, T.TS_NAME

many thanks

 

Helen

 

1 ACCEPTED SOLUTION

Accepted Solutions
irtuk Outstanding Contributor.
Outstanding Contributor.
Solution

Re: SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

Hey again Helen.

CAST(TI.TC_EXEC_DATE AS DATE) AS  "Exec_Date",

Try that. The exact syntax depends on your back end database. Oracle or SQL server are two popular choices. I think that statement works on both.

Ed.

If this works for you please mark it as the accepted solution and whilst you are doing that, throw me a KUDO or two :)

Edward Moore
______________________________________________________________
Please mark this post as "accepted solution" if it solves your problem and don't forget to add KUDOS!
3 REPLIES
irtuk Outstanding Contributor.
Outstanding Contributor.
Solution

Re: SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

Hey again Helen.

CAST(TI.TC_EXEC_DATE AS DATE) AS  "Exec_Date",

Try that. The exact syntax depends on your back end database. Oracle or SQL server are two popular choices. I think that statement works on both.

Ed.

If this works for you please mark it as the accepted solution and whilst you are doing that, throw me a KUDO or two :)

Edward Moore
______________________________________________________________
Please mark this post as "accepted solution" if it solves your problem and don't forget to add KUDOS!
HellsBells Absent Member.
Absent Member.

Re: SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

Brilliant - thank you - that works!!

thanks again for this + the last one you solved

 

Helen

irtuk Outstanding Contributor.
Outstanding Contributor.

Re: SQL / Excel Report in Dashboard Analysis - remove the 00:00:00 from Execution Dates etc please

More than welcome. Keep them coming :)

Edward Moore
______________________________________________________________
Please mark this post as "accepted solution" if it solves your problem and don't forget to add KUDOS!
Top Contributors Last 30 Days