topic Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview) in QlikView

Need to convert Number into Date while loading from database(Sql Select in Qlikview)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am working on implementing a incremental load and I am stuck at a point where I need your help.

Date Format:1508818865

To convert this into proper date format, I am using below script at expression/Script level

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') + 1508818865/60/60/24))))))

However this is throwing error when I am trying to restrict year while writing SQL Select statement in Qlikview in where condition.

eg: SQL SELECT COL1,COL2,COL3,COL4 from table where

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') + 1508818865/60/60/24))))))>='2015'

Please let me know how to proceed on this. Its a bit urgent.

Thanks

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

swuehl — Mon, 19 Mar 2018 16:51:08 GMT

You can only use functions supported by your DBMS when executing a SQL SELECT query.

You can use Qlik functions in a preceding LOAD though.

Preceding Load

But this will not limit the query result on the DBMS level, so it's better to use a WHERE clause with proper SQL functions to retrieve the year.

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

Anonymous — Tue, 20 Mar 2018 03:14:25 GMT

Hi Stefan,

Thanks for your reply.

Can you please help me with the code.

Appreciate your help.

Thanks

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

wdchristensen — Tue, 20 Mar 2018 17:17:51 GMT

What type of database are you pulling (Oracle, SQL Server)?

Date Format:1508818865

What date format is that in MM/DD/YYYY?

Might try to remove the quotes from 2015 since the year function returns an integer.

Year(DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') + 1508818865/60/60/24))))))>=2015

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

wdchristensen — Tue, 20 Mar 2018 17:22:59 GMT

Or maybe

SELECT COL1,COL2,COL3,COL4 from table

where

DATE(Floor(Num(DATE(Timestamp(Timestamp('1970-01-01 00:00:00.000') + 1508818865/60/60/24))))))>='01/01/2015'

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

swuehl — Tue, 20 Mar 2018 19:33:19 GMT

As said, it will depend on your database and SQL dialect.

For example, using MySQL, it may look like

SQL SELECT COL1,COL2,COL3,COL4

FROM Table

WHERE

Year(from_unixtime(YourTimestampFieldShowingUnixTime)) >= 2015;

If in doubt, ask your local DB guru next door.

Regards,

Stefan

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

Anonymous — Wed, 21 Mar 2018 05:18:40 GMT

Hi Christensen,

I am pulling data from oracle DB.

Date Format:1508818865

When we use the query I mentioned in Qlikview, it converts to YYYY/MM/DD HH:MM:SS

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

wdchristensen — Wed, 21 Mar 2018 15:21:18 GMT

What is the error? Did solution without the year function generate the same error?

Re: Need to convert Number into Date while loading from database(Sql Select in Qlikview)

swuehl — Wed, 21 Mar 2018 15:29:00 GMT

Maybe something like

SQL SELECT COL1,COL2,COL3,COL4

FROM Table

WHERE

Year( to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(YourTimestampFieldShowingUnixTime,'SECOND'))>=2015;