Date/Time truncation

I have the following SQL query that I want to drop the timestamp from the results:

select pos_trn_ln.trn_dt,
pos_trn_ln.store_cd, pos_trn_ln.term_num,
pos_trn_ln.trn_num, pos_trn.cshr_num, pos_trn_ln.ln_tp,
pos_trn_ln.amt, pos_trn_ln.sku_num, gm_itm.UDF1, pos_trn_ln.qty
from pos_trn_ln, pos_trn, gm_itm, gm_sku
where pos_trn_ln.trn_num=pos_trn.trn_num
and gm_sku.sku_num=pos_trn_ln.sku_num
and gm_sku.itm_cd=gm_itm.itm_cd
and pos_trn_ln.trn_dt between '1-DEC-2003'
and '1-DEC-2003'
and pos_trn_ln.ln_tp in ('EMP')

The results look as follows:
TRN_DT STORE_CD TERM_NUM TRN_NUM CSHR_NUM LN_TP AMT SKU_NUM UDF1 QTY
------ --- --- --- --- -- ---- ---- --- ---
2003-12-01 00:00:00 0004 02 147 005555 EMP -11 971600976-03 NI 1
2003-12-01 00:00:00 0023 01 348 000000 EMP -4 000027160-06 CM 1

...I want to drop off the time stamp all together in the results. I thought using the TRUNC function would work like:

where pos_trn_ln.trn_dt=TRUNC(datefield) but something is wrong. Anyone?You didn't state what database your using but there is available a DATE() or TO_DATE() function in which you can also specify formatting to pull only the date.

Originally posted by heprox
I have the following SQL query that I want to drop the timestamp from the results:

select pos_trn_ln.trn_dt,
pos_trn_ln.store_cd, pos_trn_ln.term_num,
pos_trn_ln.trn_num, pos_trn.cshr_num, pos_trn_ln.ln_tp,
pos_trn_ln.amt, pos_trn_ln.sku_num, gm_itm.UDF1, pos_trn_ln.qty
from pos_trn_ln, pos_trn, gm_itm, gm_sku
where pos_trn_ln.trn_num=pos_trn.trn_num
and gm_sku.sku_num=pos_trn_ln.sku_num
and gm_sku.itm_cd=gm_itm.itm_cd
and pos_trn_ln.trn_dt between '1-DEC-2003'
and '1-DEC-2003'
and pos_trn_ln.ln_tp in ('EMP')

The results look as follows:
TRN_DT STORE_CD TERM_NUM TRN_NUM CSHR_NUM LN_TP AMT SKU_NUM UDF1 QTY
------ --- --- --- --- -- ---- ---- --- ---
2003-12-01 00:00:00 0004 02 147 005555 EMP -11 971600976-03 NI 1
2003-12-01 00:00:00 0023 01 348 000000 EMP -4 000027160-06 CM 1

...I want to drop off the time stamp all together in the results. I thought using the TRUNC function would work like:

where pos_trn_ln.trn_dt=TRUNC(datefield) but something is wrong. Anyone?|||I apologize, the DB is an Oracle 8i instance. When you say use the DATE() function, how? I've tried TO_DATE() with invalid column name errors...|||TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'))

Originally posted by heprox
I apologize, the DB is an Oracle 8i instance. When you say use the DATE() function, how? I've tried TO_DATE() with invalid column name errors...