Distinct date from timestamp

[viveksingh]

Hi Experts,

I have CALENDAR_DATE field in Time stamp format, which is like 2001-01-13 00:00:00.000000

I  extracted Date from the Time stamp with  Date(Timestamp(CALENDAR_DATE),'MMM-YY')

and the output shows as

JAN-01

JAN-01

FEB-01

JAN-02

FEB-02

FEB-02

JAN-03

FEB-03

I want this as which is unique records along with sort.

JAN-01

FEB-01

JAN-02

FEB-02

JAN-03

FEB-03

Thanks,

Vivek

[swuehl · Accepted solution]

Try

Date(Monthstart(CALENDAR_DATE),'MMM-YY')

[vinieme12]

use

DUAL(DATE#( Date(Timestamp(CALENDAR_DATE),'MMM-YY')),Num(Date(Timestamp(CALENDAR_DATE),'YYYYMM')) )

[viveksingh]

Thank you very much Stefan. It worked well..

Thanks to all Experts for guiding me.