Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

An alternative way to convert a date?

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi,

Try this

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

Regards,

Jagan.

View solution in original post

11 Replies
MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi,

Try like this

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

Regards,

Jagan.

MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi,

Try like this

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

Regards,

Jaga

Partner
Partner

Re: An alternative way to convert a date?

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

MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi,

Try this

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

Regards,

Jagan.

View solution in original post

Partner
Partner

Re: An alternative way to convert a date?

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?

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

OR

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


Hope that helps

MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi Alexis,

The integer representation of date is

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


Hope this helps you.


Regards,

Jagan.

Partner
Partner

Re: An alternative way to convert a date?

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

MVP & Luminary
MVP & Luminary

Re: An alternative way to convert a date?

Hi,


Try this


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


Regards,

Jagan.