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

[paulyeo11]

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

[surendraj]

Where You are Trying?

In script? or anything else.!!

[paulyeo11]

wow you are very good.

[paulyeo11]

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]

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]

Cool..

[Anil_Babu_Samineni]

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

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

[akshaye_c_navale]

Hi,

Try below,

In load script used below code,

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

[paulyeo11]

Hi Anil

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

Paul

[paulyeo11]

Hi Akshay

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

Thank you

Paul

[paulyeo11]

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