Date Format returns NULL value

[Anonymous]

Hi, i have a question. I have a data like this before formatting

I want to change the date format into 'MM/dd/yyyy' by using this function in load script

date(SHIP_DATE,'MM/dd/yyyy') as SHIP_DATE

But when data load is finished it returns null like this:

I also do this to another fieldname and it runs perfectly, only "SHIP_DATE" field returns null.

Anybody know what's the problem ?

really appreciate your help

Thanks

[Chanty4u]

=Date(Timestamp#(ship_date,'MM/DD/YYYY ') as ship_date

[tamilarasu]

Hi Lejours,

Try,

Date(Floor(Date#(SHIP_DATE,,'YYYY/MM/DD hh:mm:ss')),'MM/dd/yyyy') as SHIP_DATE;

[avinashelite]

Try like this:

Date(Date#(ship_date,'MM/DD/YYYY ')

[#5]

the Ship_date may be coming as text. you required to use date(Date#(SHIP_DATE,'yyyy/MM/dd'),'MM/dd/yyyy')

[Chanty4u]

i think the date fileds are not same names...as u wil give new fieldname

[tresesco · Accepted solution]

May be your date field is having text. Try something like:

Date(Floor(Date#(Trim(SHIP_DATE), 'YYYY/MM/DD hh:mm:ss')), 'MM/DD/YYYY') as SHIP_DATE

[Chanty4u]

Tmp:
Load *, Date(Date#(SHIP,'YYYYMMDD'),'MM/DD/YYYY') as NewShiftDate,

Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewClockInOut,
Num(Timestamp(Timestamp#(CLOCK_IN_OUT,'MM/DD/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as NewClockInOutNum;

[Anonymous]

change your date fomat to

Date(SHIP_DATE,'MM/DD/YYYY') as SHIP_DATE

[engishfaque]

Dear Lejours88,

Please use listed below script,

Date(Floor(Timestamp#(SHIP_DATE, 'YYYY/MM/DD hh:mm:ss')), 'DD/MM/YYYY') as SHIP_DATE,

Time(Frac(Timestamp#(SHIP_DATE, 'YYYY/MM/DD hh:mm:ss')), 'hh:mm:ss') as SHIP_TIME

Kind regards,

Ishfaque Ahmed