topic Re: An alternative way to convert a date? in QlikView

An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 14:49:06 GMT

In my data set I appear to be receiving a date in an unusual format. It's numeric (e.g. 1355833734) and it turns out that it's the number of seconds since 1/1/1970.

Below is one way of converting it:

ConvertToLocalTime(Date(MakeDate(1970, 1, 1) + (call_date / 86400)), 'Athens') as CallDate

Are you aware of a better way that makes it less "regional"

Thanks in advance

Alexis

Re: An alternative way to convert a date?

jagan — Sat, 19 Oct 2013 14:59:30 GMT

Hi,

Try like this

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)))

Regards,

Jagan.

Re: An alternative way to convert a date?

jagan — Sat, 19 Oct 2013 15:02:15 GMT

Hi,

Try like this

=Time(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'MM/DD/YYYY HH:MM:SS')

Regards,

Jaga

Re: An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 15:09:18 GMT

Thanks for the reply Jagan - this second reply of yours gave me:

12/30/1899 12:12:54

which is incorrect - it should be a date in December 2012!

I need to take the original long number and create:

a) Date in the format dd/mm/yyyy (exactly as my local settings dictate)

b) Time in HH:MM:SS format..

Re: An alternative way to convert a date?

jagan — Sat, 19 Oct 2013 15:13:48 GMT

Hi,

Try this

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'M/D/YYYY h:m:s')

Regards,

Jagan.

Re: An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 15:36:14 GMT

Thanx Jagan,

So I guess with:

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400)), 'D/M/YYYY')

So the "integer" representation of the date will then be something like:

=Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400),'##0')

Thanks for your help

Alexis

Re: An alternative way to convert a date?

CELAMBARASAN — Sat, 19 Oct 2013 15:48:24 GMT

Hi,

I guess you need to subtract one day. because as per your statement it starts from 1/1/1970. here you have included that day too.

=Date(Num(Date(MakeDate(1969, 12, 31)) + (1355833734 / 86400)), 'D/M/YYYY')

=Date(Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400) - 1), 'D/M/YYYY')

Hope that helps

Re: An alternative way to convert a date?

jagan — Sat, 19 Oct 2013 15:59:56 GMT

Hi Alexis,

The integer representation of date is

=Num(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))

Hope this helps you.

Regards,

Jagan.

Re: An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 16:05:19 GMT

That is not correct Jagan - it gives a decimal value not integer..

Re: An alternative way to convert a date?

jagan — Sat, 19 Oct 2013 16:23:57 GMT

Hi,

Try this

=Floor(Date(MakeDate(1970, 1, 1)) + (1355833734 / 86400))

Regards,

Jagan.

Re: An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 16:34:45 GMT

Yes that is what I did in the end!

Re: An alternative way to convert a date?

alexis — Sat, 19 Oct 2013 16:37:04 GMT

I think it is correct as it was Celambarasan as it "seconds" from 1/1/1970 - I am assuming it is from 01/01/1970 00:00:00