Converting Numeric to Date to use Filters

#1

Hello Everyone,

I need to add a filter using a between dates. But the table I’m using stores dates in type numeric. So apparantly Metabase can not identify this as a Date.

I tried to do this in the select:

  1. CONVERT(date, CAST(CE061_DT_PGTO AS varchar), 112) AS DATA,

But when I tried to add the filter it says that there is no valid fields.

This msg appeard somewhere else but I don’t remember where: Operand type clash: datetime2 is incompatible with numeric

Someone else have deal with this situation?

Thanks,

#2

Hi @leonardo
Are you storing the dates as timestamp or some custom way as bigint?
If you’re storing as timestamp, then you should be able to set the Field Type to “UNIX Timestamp” in Admin > Data Model > (database) > (table) > (column) :gear:
If you’re storing in some custom way, then you would have to create a database View, which does the conversion, so Metabase only sees that View as a table.

#3

Hi @flamber

In this table date is stored as type numeric.

So a way would be creating a View using convert?

CONVERT(date, CAST(CE061_DT_PGTO AS varchar), 112) AS DATA

And then calling It in my query?

#4

@leonardo
What database are you using?
If you create a View in your database, then Metabase just sees that as a regular table, so you wouldn’t need to use Native Query (SQL) in Metabase, but could just use the Custom Query Browser.
If you cannot make a View, then you would need to use simple Date filter (and have two of those) instead of using Field Filter, since Field Filters creates all the logic, so you don’t really have much control over it (like converting things).

#5

@flamber
I’m using SQL Server.

I create previously a variable like: {{CE061_DT_PGTO}} , then I put 20190605 and It returns the result of only one day.
When you say two Date filter you mean adding one more this variable?

Sorry for my bad english and if I’m misleading something :sweat_smile:

#6

@leonardo

I would recommend that you create a View in your database, so Metabase sees the data in a correct way - with real date columns. That would fix all your problems.

If you want to use your current table with the wrong column type, then you would use the regular Date filter - but it currently only supports a single date, so you would need to use two filters - and then convert the filter value from '2019-06-05' to 20190605:

SELECT * FROM dbo.mytable
WHERE 1=1
AND CE061_DT_PGTO>=CAST(CONVERT(VARCHAR(8), CAST({{filter1}} AS DATE), 112) AS INT)
AND CE061_DT_PGTO<=CAST(CONVERT(VARCHAR(8), CAST({{filter2}} AS DATE), 112) AS INT)

If the example does not work you, then use a View.

#7

@flamber

Hello flamber,
I tried use those filters and returned this: Explicit conversion from data type int to date is not allowed.

I will try creating a View. Something like that?

CREATE VIEW v_data AS
SELECT CONVERT(date, CAST(DT_PGTO AS varchar), 112) AS DATA,
FROM dbo.mytable
WHERE 1=1 ;

or

select convert(varchar, cast(convert(varchar, DT_PGTO) as datetime), 101) AS DATA
from
dbo.mytable
where 1=1

both seems to return, but I don’t know which one can be properly used.

#8

@leonardo
I don’t know what type of filter you’re using - Text, Number, Date or Field Filter.
My example requires those two filters to be simple Date.
You might need to play around with it.

And I don’t know your data, so I wouldn’t know what exactly to do with the view.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql
But this is not the right forum for general SQL questions - use StackOverflow.com or similar for that.