Date

[smilingjohn]

HI all

Currently my Date format for the field " Datefield" is like this

24-MAY-17 01.30.58.000000000 PM

i want to convert this this Datefield like   24-05-2017

How do i achaive this ?

Can someone please help me

Thanks in advance

[its_anandrjs]

Try to write this

LOAD *,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

];

[its_anandrjs]

Find the attached

LOAD

*,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

];

[smilingjohn]

Hi Anand

Thanks for the reply

I have many date field which are from 4 years ...

Cant we have a date conversion instead of above ?

I mean i want to convert this at the one line script itself  ..

Like this below

Date#(Num(Datefield,'DD-MM-YYY') as Datefield

I hope u undertood what i am tryong to say

Thanks

[its_anandrjs]

From your date string 24-MAY-17 01.30.58.000000000 PM first you have to cut the date string that is 24-MAY-2017 and this will do for all rows in that field and then convert it into proper date for that is you need 24-05-17 this is also do for all rows.

Hope you are looking for this and this is what you are looking for.

LOAD

*,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

23-MAY-17 05.30.58.000000000 PM

22-MAY-17 08.30.58.000000000 PM

];

[smilingjohn]

Hi Anand

PLease find the attachement of the QVW file ..

I want the output for all the Datefields

In numbers like below .

01-12-2017 12:34:00

Thanks in advance

[antoniotiman]

Hi John,

may be this

Date(Date#(BillingDate,'DD-MMM-YY hh.mm.ss.fff TT'),'DD-MM-YYYY hh:mm:ss')

Regards,

Antonio

[its_anandrjs]

Try with Timestamp

Timestamp(Timestamp#(left(DateString,18),'DD-MMM-YY hh.mm.ss'),'DD-MM-YY hh:mm:ss') as NewDate

[smilingjohn]

Thanks Anand

You rock

That works  but i need the out put with year 2017 .

where as now it is giving year like 17 ...

[its_anandrjs · Accepted solution]

Find the attached