SQL timestamp conversion issue in a where clause statement

[BrunPierre]

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.

[rubenmarin1]

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.

[BrunPierre]

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.

[rubenmarin1]

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?

[BrunPierre]

@rubenmarin1 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

[rubenmarin1 · Accepted solution]

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)

[BrunPierre]

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

Many thanks.