Date ?

[#1]

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".

[Anonymous]

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))

[#3]

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

[#4]

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

[#5]

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

[Anonymous]

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.

[#7]

hi...

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