Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

How to convert date format DD-MM-YYYY into Year ?

How to convert date format DD-MM-YYYY into Year ?


Hi All


I have import a raw data file from Excel to Qli View.


I try to convert the imported field name = [Expected Close Date] in order year format YYYY.


I have try to using the below expression :-


//   text(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

//   dual(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

//   year(today()) - year([Expected Close Date]) + 1                                         as Year,

//   num(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

//   dual(right(Year(date#([Expected Close Date],'DD/MM/YYYY')), 2),'00') as Year,

//   Right(year([Expected Close Date]),2)          as Year,


All the above not able to work.


Can some one advise me.


Paul Yeo

23 Replies
surendraj
Specialist
Specialist

Where You are Trying?

In script? or anything else.!!

paulyeo11
Master
Master
Author

wow you are very good.

paulyeo11
Master
Master
Author

Hi Surendra

I using the attach QVW file to try your script. Any way now it is okay Anil already give me solution. Thank you

Paul

sannidhikumar
Creator
Creator

hi Yeo Poh,

Try this in scripting.

Date(Date#([Expected Close Date], 'MM-DD-YYYY')) as Year or

Year(Date#([Expected Close Date], 'MM-DD-YYYY')) as Year

surendraj
Specialist
Specialist

Cool..

Anil_Babu_Samineni

Great, This case Avinash solution works as expected, Some changes with spell mistake

SubField([Expected Close Date],'-' ,3) as Year


Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
akshaye_c_navale

Hi,

Try below,

In load script used below code,

YEAR(REPLACE([Expected Close Date],'-','/')) AS [Expected Close Date]

paulyeo11
Master
Master
Author

Hi Anil

You are good , you try every approach. Yes Avinash script is better , for those missing value it return as 0.

Paul

paulyeo11
Master
Master
Author

Hi Akshay

your also working. but for those missing value , it does not return any value.

Thank you

Paul

paulyeo11
Master
Master
Author

Hi Anil,

Can you please give me Month field script.

num(month([Expected Close Date])) as [month],

I assume the script should be like able , but it does not work.

Paul Yeo