topic Re: how to get the month field from this one <# YYYY-MM-DD HH:MM:SS jhbj> in QlikView

how to get the month field from this one <# YYYY-MM-DD HH:MM:SS jhbj>

Thu, 20 Jun 2013 18:52:20 GMT

hi i am having a date field which looks like <# YYYY-MM-DD HH:MM:SS jhbj> from this one how can i get the seperate year and month fields

Thu, 20 Jun 2013 19:12:27 GMT

In script:

MakeDate(RIGHT(LEFT(datetest,7),4),RIGHT(LEFT(datetest,10),2),RIGHT(LEFT(datetest,13),2)) as date

with datetest as your original date field and date as the expected output

Fri, 21 Jun 2013 04:00:17 GMT

Getting month and year dynamically

Month:

= month(date(left('2013-04-01 11:05:20 AM',index('2013-04-01 11:05:20 AM',' ')-1)))

Year:

year(date(left('2013-04-01 11:05:20 AM',index('2013-04-01 11:05:20 AM',' ')-1)))

Fri, 21 Jun 2013 06:07:39 GMT

YOU CAN ALSO TRY THIS

YEAR(Date(date#(mid(DATEFIELD,3,20),'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYY')) AS YEAR

MONTH(Date(date#(mid(DATEFIELD,3,20),'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYY')) AS MONTH

EXAMPLE--

YEAR(Date(date#(mid('# 2013-06-12 10:15:10 jhbj',3,20),'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYY')) AS YEAR

MONTH(Date(date#(mid('# 2013-06-12 10:15:10 jhbj',3,20),'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYY')) AS MONTH