How to build this query with metabase

Hello,

I am trying a question in metabase : List customers which ordered this year for the first time;

I have created this question :

Now I can't figure how to enter the year number to be accepted. entering 2022 create a type clash between a date and a number.

Am I missing a basic concept in the construction of my question?

Regards,

Hi @sbs
Try using Custom Expression instead, so Min([your_field])
https://www.metabase.com/docs/latest/questions/query-builder/expressions

Hello,

I can't seem to find a way around this. I did not see any date extraction function like year(mydate) in the custom expression for making a custom field.

The group seems to return a date but insists on coercing to a number when entering and fails at run time.

I will make a sql query and make it a view in my database, or maybe use directly the sql in the question, but this seems to defeat the idea of using metabase for non sql users.

@sbs
Post "Diagnostic Info" from Admin > Troubleshooting.
And the field metadata of date from Admin > Data Model > (db) > (table) > (field) :gear: > look in the URL, which ends with .../123/general, where 123 is the field ID, then go to /api/field/123

Here is the field definition:

{
    "description": null,
    "database_type": "datetime",
    "semantic_type": null,
    "table_id": 51,
    "coercion_strategy": null,
    "table": {
        "description": null,
        "entity_type": "entity/GenericTable",
        "schema": "dbo",
        "db": {
            "description": null,
            "features": ["full-join", "basic-aggregations", "standard-deviation-aggregations", "expression-aggregations", "foreign-keys", "right-join", "left-join", "native-parameters", "nested-queries", "expressions", "binning", "inner-join", "advanced-math-expressions"],
            "cache_field_values_schedule": "0 0 5 * * ? *",
            "timezone": "UTC",
            "auto_run_queries": true,
            "metadata_sync_schedule": "0 55 * * * ? *",
            "name": "InProd",
            "settings": null,
            "caveats": null,
            "creator_id": null,
            "is_full_sync": true,
            "updated_at": "2022-01-11T17:20:57.28",
            "cache_ttl": null,
            "details": {
                "additional-options": null,
                "ssl": false,
                "instance": null,
                "db": "******",
                "password": "**MetabasePass**",
                "port": 1434,
                "host": "***********",
                "tunnel-enabled": false,
                "user": "ro"
            },
            "is_sample": false,
            "id": 3,
            "is_on_demand": false,
            "options": null,
            "engine": "sqlserver",
            "initial_sync_status": "complete",
            "refingerprint": null,
            "created_at": "2022-01-11T17:20:57.149",
            "points_of_interest": null
        },
        "show_in_getting_started": false,
        "name": "tblEnteteContrat",
        "caveats": null,
        "updated_at": "2022-01-11T17:21:17.251",
        "active": true,
        "id": 51,
        "db_id": 3,
        "visibility_type": null,
        "field_order": "database",
        "initial_sync_status": "complete",
        "display_name": "TblEnteteContrat",
        "created_at": "2022-01-11T17:20:57.566",
        "points_of_interest": null
    },
    "name": "DateOuverture",
    "fingerprint_version": 5,
    "has_field_values": "none",
    "settings": null,
    "caveats": null,
    "fk_target_field_id": null,
    "dimensions": [],
    "updated_at": "2022-01-11T17:21:11.411",
    "custom_position": 0,
    "effective_type": "type/DateTime",
    "active": true,
    "nfc_path": null,
    "parent_id": null,
    "id": 371,
    "last_analyzed": "2022-01-11T17:21:17.267",
    "position": 3,
    "visibility_type": "normal",
    "preview_display": true,
    "display_name": "DateOuverture",
    "database_position": 3,
    "name_field": null,
    "fingerprint": {
        "global": {
            "distinct-count": 2171,
            "nil%": 0.0
        },
        "type": {
            "type/DateTime": {
                "earliest": "2000-06-30T16:06:00Z",
                "latest": "2022-01-10T15:36:32.743Z"
            }
        }
    },
    "created_at": "2022-01-11T17:20:58.391",
    "base_type": "type/DateTime",
    "points_of_interest": null
}

@sbs Change the field type from "No semantic type" to "Creation timestamp".

@flamber : Great tip, it fixed the problem. I have now a question not written in SQL.

Thank you.