topic Re: Date issu in QlikView

Date issu

Tue, 15 Sep 2015 11:31:51 GMT

Hi,

I've the date in my DB as yy-mm-dd (15-04-01) When i load that into qv i get a 5 digit number starting with 6.

I know that it the date as text.

So I try to set in load script

date(date#(MYDATE,'YY-MM-DD'),'YYYY-MM-DD') as [realdate], but realdate is empty after load.

If I change in load to

date(date#(MYDATE,'YYMMDD'),'YYYY-MM-DD') as [realdate], the year comes as 1970

I'm sure it's something simple but I cant figue it out.

Please help

Re: Date issu

arulsettu — Tue, 15 Sep 2015 11:35:06 GMT

this works for me

date(Date#('15-04-01','yy-mm-dd'),'YYYY/MM/DD')

Re: Date issu

Tue, 15 Sep 2015 11:38:32 GMT

mydate = the columnamne in the database.

Can't get it to work.

Might be that the date is set as text in the DB

Re: Date issu

arulsettu — Tue, 15 Sep 2015 11:40:36 GMT

can you post your sample data

Re: Date issu

senpradip007 — Tue, 15 Sep 2015 11:41:58 GMT

You can try with

MakeDate(2000+left(MYDATE, 2), mid(MYDATE, 4, 2), right(MYDATE, 2)) as realdate

Re: Date issu

maxgro — Tue, 15 Sep 2015 11:44:55 GMT

post a .qvw where you load some data from your db

load

MYDATE,

text(MYDATE),

date(date#(MYDATE, 'YY-MM-DD'),'YYYY-MM-DD') as [realdate]

from ....

Re: Date issu

sasiparupudi1 — Tue, 15 Sep 2015 11:46:39 GMT

Try

date(MYDATE,'YYYY-MM-DD') as [realdate]

Re: Date issu

prma7799 — Tue, 15 Sep 2015 11:46:56 GMT

please share some test data.

Re: Date issu

Tue, 15 Sep 2015 11:47:13 GMT

can you post some sample file so that we can able to help you

Re: Date issu

engishfaque — Tue, 15 Sep 2015 11:48:58 GMT

Dear Johan,

Please use,

Date(Floor(Date#(YourDateFieldName,'YY-MM-DD')))

Kind regards,

Ishfaque Ahmed

Re: Date issu

Tue, 15 Sep 2015 12:01:03 GMT

here it the qv file

Re: Date issu

Tue, 15 Sep 2015 12:04:50 GMT

save data in to QVD and share that qvd as well .because we need to do reload after written some sort of function

Re: Date issu

Tue, 15 Sep 2015 12:13:12 GMT

and the qvd 😃

Re: Date issu

Tue, 15 Sep 2015 12:30:22 GMT

date1 as the final output field

PFA

let me know for any concerns

Thanks

Manju

Re: Date issu

sasiparupudi1 — Tue, 15 Sep 2015 12:33:38 GMT

what is the database that your are using?

Re: Date issu

sasiparupudi1 — Tue, 15 Sep 2015 12:46:00 GMT

Unix dates require subtraction of dates 1970-01-01

try if this gives you a correct value?

date(num#(Utlastningsdatum)-num(floor(MakeDate(1970,1,1))))

Re: Date issu

settu_periasamy — Tue, 15 Sep 2015 13:44:38 GMT

Hi,

Try this.

LOAD HUFTID, 
     HULSÄT, 
     Utlastningsdatum,
     TimeStamp(Makedate(1970,1,1)+ num#(Utlastningsdatum)/86400) as Date_Time,
     Postnummer, 
     FYear, 
     fungera, 
     FMonth, 
     realdate, 
     FYearMonth, 
     Fakturadatum, 
     HUFILI
FROM
test.qvd
(qvd);