Changing date display format adding a year??

[tony_222]

I have noticed something very odd today - I have changed the the display format of a date field in a dashboard and when the data is 2024-12-31 it is adding an extra year and reporting as 2025-12-31.

If I leave the format as "Date (short month name)" it reports correctly but if I use any other date formats, it adds an extra year. I need to change the format so the date fields are recognised as dates when exported to csv.

I have tried changing the format of the raw data which feeds to the dashboard but can't get anything to work.

Has anyone else noticed this/have any ideas on how to fix?

Thanks!

[ms4446]

This sounds like a time zone and/or date parsing issue that's causing BigQuery to misinterpret the edge case of 2024-12-31. Here is potential causes and solutions:

• Time Zone Mismatches: Near the end of the year, differences in time zones can lead to misinterpretation of dates during display or exporting.
• Ambiguous Formats: Without a clear specification of the year, month, and day order, date formats can cause parsing errors.
• Data Type Issues: Ensure the raw data is correctly stored as either a DATE or TIMESTAMP data type for accurate processing.

Solutions:

• For DATE Types:

  Use PARSE_DATE for accurate interpretation:

  SELECT PARSE_DATE('%Y-%m-%d', your_date_field) AS formatted_date FROM your_table

  This ensures dates are correctly parsed and displayed.

• For TIMESTAMP Types:

  Handle time zones with FORMAT_TIMESTAMP:

  SELECT FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP(your_timestamp_field), 'Your_Timezone') AS formatted_date FROM your_table

  Make sure to replace 'Your_Timezone' with the appropriate time zone identifier.

• Dashboard Formatting:

  Directly set the desired date format (e.g., 'YYYY-MM-DD') in your dashboard settings, if the option is available.

• Creating a Calculated Field:

  For additional flexibility, you can create a calculated field in BigQuery:

  SELECT FORMAT_DATE('%Y-%m-%d', your_date_field) AS formatted_date FROM your_table

  This method allows for direct control over the date format output.

Remember:

• Replace your_date_field and your_timestamp_field with the actual names of your columns.
• Always test your changes with a subset of your data to ensure the results meet your expectations.

[tony_222]

Thanks for the response. I have tried formatting the data in a number of ways but I am still having the same issue. 

As an example, if I connect the following to a dashboard: 

select

'2024-12-31' as raw_date,

date('2024-12-31') as date_date,

PARSE_DATE('%Y-%m-%d', '2024-12-31' ) as parse_date,

FORMAT_DATE('%Y-%m-%d', '2024-12-31') as format_date

I get these results.:

  

[tony_222]

Thanks for the response. I have tried a number of date formats and I still have the same issue. For example, if I connect the below sql to a dashboard, all dates report correctly when left on the default Date (short month name). However, when I change to the format (i.e. Date d/MM/YYYY) they all report as 31/12/2025.

select

'2024-12-31' as raw_date,
date('2024-12-31') as date_date,
PARSE_DATE('%Y-%m-%d', '2024-12-31') as parse_date,
FORMAT_DATE('%Y-%m-%d', '2024-12-31') as format_date

[ms4446]

Given the SQL provided and the consistent results across different methods of date handling in BigQuery, it's clear that the issue lies outside of BigQuery's handling of dates. Here are some additional steps and considerations to further investigate and potentially resolve the issue:

Investigate Dashboard Application Behavior

1. Dashboard Application Bug: The behavior suggests a possible bug in the dashboard application's date handling logic, especially in the formatting or time zone conversion process. It's worth checking the application's release notes, support forums, or contacting support directly to see if this is a known issue.

2. Locale and Time Zone Settings: Some dashboard applications might interpret dates based on the locale and time zone settings of the server or the user's browser. Ensure these settings are correctly configured. An incorrect time zone setting could potentially cause the date to roll over to the next day, and in this case, the next year.

3. Custom Format Processing: The dashboard might have a specific way of processing custom date formats that leads to this anomaly. Review the documentation or support resources for any notes on custom date formats and their limitations or known issues.

Workarounds and Alternatives

1. Explicit Time Zone Specification in BigQuery: As a test, you could try explicitly specifying a time zone in your query to see if that influences how the dashboard interprets the dates. For example, you could convert your dates to a TIMESTAMP with a specific time zone and then format it:

   SELECT FORMAT_TIMESTAMP('%d/%m/%Y', TIMESTAMP('2024-12-31'), 'UTC') as format_date_with_timezone FROM your_table

   This might not directly solve the problem but could help identify if time zone processing is a factor.

2. Pre-Format Dates in BigQuery: Since the dashboard correctly displays dates in its default format, another workaround could be to pre-format the dates as strings in the desired format within BigQuery and then treat them as strings in the dashboard. This approach bypasses the dashboard's date formatting logic but means you lose some date-specific functionality:

   SELECT FORMAT_DATE('%d/%m/%Y', DATE('2024-12-31')) as pre_formatted_date FROM your_table

   This method ensures the date appears exactly as you format it in BigQuery, but it will be treated as text rather than a date in the dashboard.

3. Dashboard Formatting Options: Explore if there are alternative ways to specify date formats in the dashboard that might not trigger the issue. Sometimes, using a slightly different format string or configuring the format at a different level (e.g., global vs. widget-specific settings) can yield different results.

4. Alternative Dashboard Tools: If the issue is critical and cannot be resolved with the current tool, consider testing the same dataset with an alternative dashboard application to see if the problem persists. This could help determine if the issue is specific to the current dashboard software.