Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
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.
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?
@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
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)
@rubenmarin Fantastic! I now understand your concept, and it's very helpful.
Many thanks.