Date Format

[danielnevitt]

Hi,

I have the below sql and I would like to specify the date for field aps.bl_dt as dd/mm/yyyy.  Currently it returns format dd/mm/yyyy hh:mm:ss.

Any helps will be much appreciated.

Regards,

Daniel

SELECT aps.bl_dt, aps.title_transfer_dt, aps.strategy_num, aps.pcr_num, aps.trade_num, aps.modify_dt, aps.title_transfer_dt_ind, aps.delete_dt, aps.delete_initials, aps.actual_qty/100 actual_qty, aps.actual_qty_uom_cd, aps.api_gravity, aps.specific_gravity,
so.desk_cd, so.office_cd,
CASE ph.trade_status_ind
WHEN 'B' THEN 'Partially Cancelled'
WHEN 'C' THEN 'Cancelled'
WHEN 'E' THEN 'Partially Finalized'
WHEN 'F' THEN 'Finalized'
WHEN 'L' THEN 'PL Run'
WHEN 'N' THEN 'New Trade'
WHEN 'P' THEN 'Parceled'
WHEN 'Q' THEN 'Partially Realized'
WHEN 'R' THEN 'Realized'
WHEN 'V' THEN 'Vouchered'
ELSE 'Other'
END 'trade_status_ind',
c.co_cd

FROM tempest_rpt_prod.dbo.AUDIT_PHYSICAL_SETTLEMENT aps
left outer join tempest_rpt_prod.dbo.STRATEGY_OWNER so on (so.strategy_num = aps.strategy_num)
left outer join tempest_rpt_prod.dbo.PHYSICAL_HDR ph on (ph.hdr_num = aps.hdr_num)
left outer join tempest_rpt_prod.dbo.CONTRACT c on (c.contract_num = ph.contract_num)
where aps.modify_dt>= '2016-01-01'
and so.office_cd in ('Non-US')

[sunny_talwar]

May be fix it in the SQL itself using SQL functions or use preceding load in QlikView to fix it:

LOAD *,

          Date(Floor(aps.bl_dt), 'DD/MM/YYYY') as DateField;

SELECT aps.bl_dt, aps.title_transfer_dt, aps.strategy_num, aps.pcr_num, aps.trade_num, aps.modify_dt, aps.title_transfer_dt_ind, aps.delete_dt, aps.delete_initials, aps.actual_qty/100 actual_qty, aps.actual_qty_uom_cd, aps.api_gravity, aps.specific_gravity,

so.desk_cd, so.office_cd,

CASE ph.trade_status_ind

WHEN 'B' THEN 'Partially Cancelled'

WHEN 'C' THEN 'Cancelled'

WHEN 'E' THEN 'Partially Finalized'

WHEN 'F' THEN 'Finalized'

WHEN 'L' THEN 'PL Run'

WHEN 'N' THEN 'New Trade'

WHEN 'P' THEN 'Parceled'

WHEN 'Q' THEN 'Partially Realized'

WHEN 'R' THEN 'Realized'

WHEN 'V' THEN 'Vouchered'

ELSE 'Other'

END 'trade_status_ind',

c.co_cd

FROM tempest_rpt_prod.dbo.AUDIT_PHYSICAL_SETTLEMENT aps

left outer join tempest_rpt_prod.dbo.STRATEGY_OWNER so on (so.strategy_num = aps.strategy_num)

left outer join tempest_rpt_prod.dbo.PHYSICAL_HDR ph on (ph.hdr_num = aps.hdr_num)

left outer join tempest_rpt_prod.dbo.CONTRACT c on (c.contract_num = ph.contract_num)

where aps.modify_dt>= '2016-01-01'

and so.office_cd in ('Non-US');

[danielnevitt]

Thanks Sunny.

Ideally I would like to fix using the SQL functions (if possible).

Regards,

Daniel

[sunny_talwar]

In that case, you can use TRUNC() function in SQL