Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Where You are Trying?
In script? or anything else.!!
wow you are very good.
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
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
Cool..
Great, This case Avinash solution works as expected, Some changes with spell mistake
SubField([Expected Close Date],'-' ,3) as Year
Hi,
Try below,
In load script used below code,
YEAR(REPLACE([Expected Close Date],'-','/')) AS [Expected Close Date]
Hi Anil
You are good , you try every approach. Yes Avinash script is better , for those missing value it return as 0.
Paul
Hi Akshay
your also working. but for those missing value , it does not return any value.
Thank you
Paul
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