topic Re: SQL timestamp conversion issue in a where clause statement in QlikView

SQL timestamp conversion issue in a where clause statement

BrunPierre — Mon, 26 Jan 2026 21:26:37 GMT

Hello Everyone!

Trying to convert this "1605291725" which I believe to be in this format 'YYMMDDHHMM' to 'DD/MM/YYYY'.

This is works fine DATE(DATE#(LEFT(DATE_TIME,6),'YYMMDD'),'DD/MM/YYYY') when filtering through QVDs, however,

I can't seem to translate it into SQL when I try to spool new data from the source database i.e. with a where clause

while refreshing data.

Please help!

Kind regards.

Re: SQL timestamp conversion issue in a where clause statement

rubenmarin — Sat, 20 Feb 2021 08:58:52 GMT

Hi @BrunPierre, maybe it's stored as number in database, have you tried to filter it as ">=2100000000" to retrieve 2021 data?.

Once you make it work with a fixed value it will be easier to make it dynamic.

Re: SQL timestamp conversion issue in a where clause statement

BrunPierre — Sat, 20 Feb 2021 16:16:37 GMT

Thanks for response, but your suggestion wasn't too clear. Please elaborate and secondly, the date field is indeed numeric with a YYMMDDHHmm format.

Thank you.

Re: SQL timestamp conversion issue in a where clause statement

rubenmarin — Sun, 21 Feb 2021 08:05:04 GMT

Hi @BrunPierre, I meant that if the data in sql is a number, not a date, you need to use a number to filter.

What filter are you using in SQL?

Re: SQL timestamp conversion issue in a where clause statement

BrunPierre — Sun, 21 Feb 2021 08:52:35 GMT

@rubenmarin convert(datetime, (convert (int, DATE_TIME)), 6), but it throws an error converting.

Perhaps you could suggest a syntax to use number to filter?

Thanks

Re: SQL timestamp conversion issue in a where clause statement

rubenmarin — Sun, 21 Feb 2021 12:15:02 GMT

What I'm saying is that instead of trying to convert that value to a date, use the integer as filter, if you have a date or timestamp you can convert as

LET vFilter = timestamp(DateToFilter,'YYMMDDhhmm')

And use hat value in where clause

Where field>=$(vFilter)

Re: SQL timestamp conversion issue in a where clause statement

BrunPierre — Sun, 21 Feb 2021 18:15:02 GMT

@rubenmarin Fantastic! I now understand your concept, and it's very helpful.

Many thanks.