Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Unanswered: 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?

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Date/Time truncation

    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?

  3. #3
    Join Date
    Oct 2003
    Posts
    54
    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...

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •