Display Correct Date Format

[prabunakil]

Hi All

I have a problem in extracting Birthday Month.

example:

i have different set of date set in database as string for birthday :

Scenario 1: 08.08.2018

Scenario 2: 8.08.2018

Scenario 3: 8.8.2018

Scenario 4: 08.8.2018

How can i set the expression to capture the month = 8.

Please advise.

[rwunderlich]

Month(Date#(indate, 'M.D.YYYY')) as Month

The format will pick up both single and double digit Day and Month.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[prabunakil]

Did not work. result all show as '-'

[rwunderlich]

I tested using the data you posted. Can you post the script you tried?

-Rob

[prabunakil]

    DateOfBirth,

    //Month(Date#(DateOfBirth, 'M.D.YYYY')) as BirthdayMonth, <- this suggested by you but no working too.

    Mid(DateOfBirth,4,2) as [BirthdayMonth],   <- this the one i current using which is wrong.

[rwunderlich · Accepted solution]

The data in this screenshot shows "-" as the separator in the format pattern. Your first post used "." as the separator. Modify my example to use the format pattern actually used in your data.

Also is looks like it should be day first:

Month(Date#(DateOfBirth, 'D-M-YYY')) as BirthdayMonth

-Rob

[Arthur_Fong]

Hi Prabu,

Refer attached and apply the function SubField to grab strings from DOB column.

Thanks and regards,

Arthur Fong

[prabunakil]

thank you Rob, is working fine now.