How to build this query with metabase


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?


Hi @sbs
Try using Custom Expression instead, so Min([your_field])


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.

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.