Timestamp field issue

[gayatri7]

Hi All,

I have a field which has timestamp value '4/8/2010  2:43:29 PM'. I wanted to get the month & year combination out of all dates. I have tried both the  logics but still not getting the desired result.

1.     date(monthstart(date#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,

2.     date(monthstart(timestamp#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,

Could anyone please help me to understand if I am missing anything.

Attaching the sample qvw and data file

Thanks

[sunny_talwar]

May be try this

Date(MonthStart(TimeStamp#(ClaimSubmitDate, 'M/D/YYYY  h:mm:ss TT')), 'MMM YYYY') as Claim_Month,

few things to note

1) M is for Month and m is for minute... in TimeStamp#() you use m for month

2) It seems that there are two spaces between Date and time portion... if that is true, make sure to use two spaces within TimeStamp#() interpretation or you can try this

Date(MonthStart(Date#(SubField(ClaimSubmitDate, ' ', 1), 'M/D/YYYY')), 'MMM YYYY') as Claim_Month,

[sunny_talwar · Accepted solution]

Based on the sample file you provided... this should work because your date is already understood as date by QlikView... you don't need an interpretation function here (Date#() or TimeStamp#())

Date(MonthStart(ClaimSubmitDate), 'MMM YYYY') as Claim_Month,

[gayatri7]

Thank u so much!! U r always a Saviour!!