topic Re: convert date in script help in QlikView

convert date in script help

Thu, 12 Mar 2015 09:17:19 GMT

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

Re: convert date in script help

luciancotea — Thu, 12 Mar 2015 09:25:43 GMT

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

Re: convert date in script help

puttemans — Thu, 12 Mar 2015 09:28:45 GMT

Hi,

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

Re: convert date in script help

avinashelite — Thu, 12 Mar 2015 09:31:07 GMT

try like this:

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

Re: convert date in script help

Thu, 12 Mar 2015 09:38:32 GMT

Hi Avinash

does´nt work

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

Re: convert date in script help

Thu, 12 Mar 2015 09:39:12 GMT

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.

Re: convert date in script help

Thu, 12 Mar 2015 09:52:30 GMT

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

?????

Re: convert date in script help

puttemans — Thu, 12 Mar 2015 10:01:37 GMT

How about:

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

Re: convert date in script help

Thu, 12 Mar 2015 10:05:28 GMT

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

Re: convert date in script help

Thu, 12 Mar 2015 10:09:45 GMT

Sorry doesnt change to dd-mm-yyyy

Re: convert date in script help

puttemans — Thu, 12 Mar 2015 10:12:33 GMT

Ha no, my bad, I did not put the correct format.

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

Re: convert date in script help

luciancotea — Thu, 12 Mar 2015 10:16:22 GMT

Then your format is 'YYYY-MM-DD hh:mm:ss.fffffff'

Re: convert date in script help

Thu, 12 Mar 2015 10:21:17 GMT

Hi All

with help from you all and the last from Lucian, at the top of discussion I tried this

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

and it worked

Thanx a lot all