convert date in script help

[#1]

Hi

Suddenly one of my datafields(which I cant change myself)is altered from showing the dateformat as

dd-mm-yyyy to showing my date as yyyy-mm-dd-hh-mm-ss-sssssssssssss

Please help to make it as dd-mm-yyyy

Something like

load

[DATE],

LEFT([DATE],10)dd-mm-yyyy as DATE

[luciancotea]

Use date#() function to transform dates.

LOAD

date#([DATE], 'YYYY-MM-DD-hh-mm-ss-sssssssssssss') as myDATETIME

date(date#([DATE], 'YYYY-MM-DD-hh-mm-ss-sssssssssssss'), 'YYYY-MM-DD') as myFormattedDATETIME

floor(date#([DATE], 'YYYY-MM-DD-hh-mm-ss-sssssssssssss')) as myDATE

FROM TABLE1;

Notes:

date#() is a transforming function

date() is a formatting function

'mm' is for minutes

'MM' is for months

[puttemans]

Hi,

Attached document may help you (now and in the future)

[avinashelite]

try like this:

Date(floor(datefield),'DD-MM-YYYY') as feildname

[#5]

Hi Avinash

does´nt work

My date field looks like this at present 2015-03-12 03:08:51.9470000

[#6]

Try to transform the date to the needed format using date#() function.

date#(datefield,'DD-MM-YYYY') as Date_Field

If you do formatting directly it may lead to some interruptions later. So it always better to do transforming first and then do formatting on top based on your needs or requirements.

[#7]

Hi Lathaa

Load
[DAGSDATO],
date#(DAGSDATO,'DD-MM-YYYY')as CDAGSDATO
RESIDENT [VSIG];
this is what I tried - but it still gives me the date as yyyy-mm-dd

the DAGSDATO field is yyyy-mm-dd formatted

?????

[puttemans]

How about:

Date( Date#( DAGSDATO, 'yyyy-mm-dd-hh-mm-ss-sssssss'), 'YYYY-MM-DD') as Date ?

[#9 · Accepted solution]

Oops try this

Date(Date#(Date_field,'OriginalDateformat'),'your_neededformat') as date_field

date(date#(DAGSDATO, 'YYYY-MM-DD hh:mm:ss.fffffff')),'DD-MM-YYYY') as CDAGSDATO

Regards

Lathaa

[#10]

Sorry doesnt change to dd-mm-yyyy