Date functions not working

[#1]

Hi All,

I have a field called DateAssign in the database. Its stored in NVARCHAR2(200) format in an oracle database. I am trying to use simple date functions like Year, Month etc but its not working.

Eg.

Load Year(DateAssign) as "YearD";  After the reload nothing is coming up in the YearD field.

Can anyone please suggest anything?

Thanks !

[CELAMBARASAN · Accepted solution]

Before applying date functions you need to format it to date if it is not in standard date format

Example : date format stored in db is MM-DD-YYYY

then

Year(Date#(DateField,'MM-DD-YYYY'))

[#3]

Thanks for your reply. I actually used Year(Date(DateField,'MM-DD-YYYY')), but it didn't work. Don't know the reason.

Thanks again for your reply.

[MayilVahanan]

Hi

Date#() function takes string and convert into dateformat

Date() function takes number and convert into dateformat..

nVarchar() is string format..So date() function not suitable here..

Regards,

R.MayilVahanan