topic Re: Date ? in QlikView

Date ?

Sun, 12 Apr 2015 08:35:37 GMT

Hi...

I have date and time combined field in my SQL db. they have used Longtodate function extract the date in DB. how to get the exact date in QLikview??

Eg : My Date filed shows the value "1419310077901".

Re: Date ?

krishna20 — Sun, 12 Apr 2015 09:16:22 GMT

Hi,

Change your date format like below

=Date((CLM_CR_DT),'DD/MM/YYYY hh:mm:ss')

Re: Date ?

satishkurra — Sun, 12 Apr 2015 10:23:36 GMT

For any kind of date problems it is recommended to use the below

Date(Date#(Date,'YYYYMMDD')) as Date,

Date# converts text to number and Date converts number to text.

Thanks

Satish

Re: Date ?

Anonymous — Sun, 12 Apr 2015 11:31:48 GMT

Can you explain what exactly date and time is represented by value '1419310077901'?

Most likely you need the timestamp#() function, but I can't guess the format...

Re: Date ?

Anonymous — Sun, 12 Apr 2015 11:45:23 GMT

Hi try like this

Date(Date#(DateField, 'YYYYMMDD'), 'M/D/YY')

Get the Dates Right

Hope this will help you

Re: Date ?

Sun, 12 Apr 2015 12:20:44 GMT

even I am nt able to guess the format.when I extract the table from DB.it gives me the date in this format. and they are using LONGTODATE function inorder to extract the date.

Re: Date ?

Anonymous — Sun, 12 Apr 2015 12:57:08 GMT

From what I read about longtodate - it is the number of milliseconds from the "beginning of time" (1970). If this is the case, this should work:

=timestamp(addyears(timestamp(YourDateField/86400000),70))

Where 86400000 is the number of milliseconds in one day.

Re: Date ?

krishna20 — Sun, 12 Apr 2015 13:03:33 GMT

Have you tried this

=Date((your_Date_Fild),'DD/MM/YYYY hh:mm:ss')

Re: Date ?

Sun, 12 Apr 2015 13:43:41 GMT

hi... I gt the date as I required.

Can yu please explain wat we are doing here exactly.??

Regards,

Reena

Re: Date ?

Anonymous — Sun, 12 Apr 2015 13:50:04 GMT

Nice Work Micheal

Re: Date ?

Anonymous — Sun, 12 Apr 2015 16:22:16 GMT

Sure.

The first step is to divide the data by 86400000 - that is to covert the number of milliseconds into the days. The number is from this:

1000 ms * 60 s * 60 min * 24 hours

If you want make it clearer in the script, instead of

YourDateField/86400000

you can write same thing in a different way

YourDateField/24/60/60/1000

Next step is to convert it in date-time by using timestamp() function

timestamp(YourDateField/86400000)

But we're not there yet, because for QlikView (and Excel) time starts in 1900, not in 1970. Need to move 70 years forward:

addyears(timestamp(YourDateField/86400000),70)

This returns what you need, but in my testing it showed only date without time. To get date and time, I added one more timestamp():

timestamp(addyears(timestamp(YourDateField/86400000),70))

Re: Date ?

Mon, 13 Apr 2015 07:44:36 GMT

ohh...great.. .!! Thanks a ton Micheal.. itz a great work... !!!

Re: Date ?

Tue, 14 Apr 2015 08:18:52 GMT

Hii..

Micheal...

am a able to achieve the date bt the date is not correct as I required.

for example

for the db date = "1422776437800" it is giving me the value "1/30/2015 7:40:37 AM" which is not correct, actual date should be " Feb 1, 2015 11:40 AM".

I think we have to make some changes in expression used.

Regards,

Reena

Re: Date ?

Tue, 14 Apr 2015 08:30:20 GMT

a time difference of 2 days and 4 hours is there in all the time conversion.

Re: Date ?

Anonymous — Tue, 14 Apr 2015 12:38:55 GMT

You see, you do know what result to expect after all .

OK, I can't tell what is the reason, maybe I didn't find some nuance of the longdate function rule - but if the difference is consistent, it is easy to fix. Just add this difference to the expression. If it is exactly 52 hours (two days and four hours), the correction will be

52/24:

=timestamp(addyears(timestamp(YourDateField/86400000),70) + 52/24)

If you find out about the rules, let us know.

Re: Date ?

Tue, 14 Apr 2015 13:23:47 GMT

hi...

Yeah...I have done the same... bt don't know y it ws showin that difference.