I’m working with Teradata SQL, and I need to create columns that display only the year (like 2025) or month (like 6) extracted from a date field currently formatted as an integer. When I import this data into Power BI, it alters the year into an unexpected format, such as 01/04/1905. Is there a method to ensure that these values are recognized as year columns when loaded into Power BI? I want to avoid the hassle of manually parsing the year and month fields in Power BI, so it would be much better to handle this conversion directly within my SQL query. I’ve attempted storing the year as both integer and varchar, but the issue persists during the conversion in Power BI.
Have you consdiered using DATE_FORMAT function instead? It pairs well in Teradata for formatting dates explicitly so that CAST() function aren’t needed in Power BI. Just do SELECT DATE_FORMAT(your_date_column, 'YYYY') AS Year, DATE_FORMAT(your_date_column, 'MM') AS Month. This keeps it direct & neat without complex conversions.
When working with Teradata SQL, ensure your date columns are properly converted before importing them into Power BI. You can utilize the EXTRACT function to derive year or month from your date column. For example, you can write SELECT EXTRACT(YEAR FROM your_date_column) AS Year, EXTRACT(MONTH FROM your_date_column) AS Month. Before bringing it into Power BI, make sure these extracted values are being exported as integers rather than strings, as this could be where the conversion mishap is occurring. This should help in preserving the numerical format when Power BI processes the data.
Hey Ava89, have you tried tinkering with how Power BI is interpreting these columns? Sometimes, altering the data type in Power BI directly can bypass odd date conversions. Maybe after importing, you set them explicitly as “Whole Number” columns in Power BI? Curious if that might help in your case!
Another approach you could try is utilizing the TO_CHAR function in Teradata SQL to handle the date conversion directly within your query. For instance, you can convert your date field by using SELECT TO_CHAR(your_date_column, 'YYYY') AS Year, TO_CHAR(your_date_column, 'MM') AS Month. This formatting should produce string outputs which you can then cast to integers if needed before importing into Power BI. This might prevent Power BI’s automatic reformatting and ensure you get the desired columns as discrete year and month values.
Perhaps experiment by first creating a view in SQL with the formatted year and month. Use CAST or CONVERT for consistency, then link this view directly in Power BI. This might avoid the conversion issue as PBI just reads data from the view and retains the original formatting.