conversion of date

[upaliwije]

Hi

In my oracle table the date is stored in text format like  01-JAN-2015 (paid_date)

I want to link this Paid_date to master calendar. How can I do it? should I convert the paid_date to date format? if so pls help me

[rubenmarin]

Hi upali, you can do that or use a replace() or applymap() function to 'translate' month to numbers, ie:

Replace(paid_date, 'JAN', '01','FEB','02'.....)

For applymap it can be:

Map_Months:

Mapping LOAD * Inline [

     Month, Number

     JAN, 1

     FEB, 2

];

Data:

LOAD ...

     MakeDate(Right(paid_date, 4), ApplyMap('Map_Months', Mid(paid_date, 4, 3)), Left(paid_date, 2)) as paid_date,

...

Instead of Right, Mid and Left functions you can also use Subfield(paid_date, '-', Number1-3)

[Anonymous · Accepted solution]

date ( date#('01-JAN-2015' , 'DD-MMM-YYYY' ) , 'DD MMM YYYY' )

[MarcoWedel]

Hi,

Date#() is the function you are looking for, as already proposed by bill.markham (like in your thread Character Field convert to date format by maxgro and jagan)

LOAD Date#(paid_date, 'DD-MMM-YYYY') as paid_date
FROM yourSource;

will load the paid_date field as date, if your MonthNames are set accordingly

(SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';).

hope this helps

regards

Marco

[kavita25]

Hi,

Check the given application

[Colin-Albert]

Date# converts text to dates. Date#(DD-MMM-YYYY)

Full details can be found in these blogs

The Date Function

Get the Dates Right

[maxgro]

you should look at the data type of your oracle column

if it's a VARCHAR2, you need date# in QlikView

if it's a DATE usually you don't need any interpretation function in QlikView

[engishfaque]

Dear Upali,

Use this script in Load,

Date(YourDateFieldName, 'DD-MMM-YYYY') as Paid_Date

Kind regards,

Ishfaque Ahmed

[upaliwije]

Thanks all