powerbi - How can I create Year and Month columns in SQL that seamlessly translate into Power BI? - Stack Overflow

Using Teradata SQL, am trying to create columns showing only year (e.g. 2025) or month (e.g. 6) extracted from a datestamp. Currently stored as int

Upon importing to Power BI, it'll reconfigure that year number to something like 01/04/1905.

Is there a way I can have this just import straight into Power BI as a column recognised as a year?

I'm trying to avoid having to manually extract the month, year etc within Power Bi, would rather just do that conversion in SQL have it imported as is. Thanks!

(tried storing 2025 both as int and as varchar, same result when converting to date in power bi)

Using Teradata SQL, am trying to create columns showing only year (e.g. 2025) or month (e.g. 6) extracted from a datestamp. Currently stored as int

Upon importing to Power BI, it'll reconfigure that year number to something like 01/04/1905.

Is there a way I can have this just import straight into Power BI as a column recognised as a year?

I'm trying to avoid having to manually extract the month, year etc within Power Bi, would rather just do that conversion in SQL have it imported as is. Thanks!

(tried storing 2025 both as int and as varchar, same result when converting to date in power bi)

• sql
• powerbi
• teradata
• datestamp

Share Improve this question Follow asked Jan 21 at 14:43 ChumptasticChumptastic 1122 bronze badges 8

• 2 Edit your question to show some example data and desired results. If you currently have two integers, year_number and month_number then you could take advantage of Teradata "IntegerDate" and do something like CAST((year_number-1900)*10000+month_number*100+1) AS DATE. Or you could convert to VARCHAR, concatenate, and then CAST to DATE. But that's a guess at what you are trying to do. – Fred Commented Jan 21 at 15:30
• I can't reproduce this behavior at all. Pulling from sys_calendar.calendar, all the integer fields in there (day_of_week, etc) show as integers. Only dates show as dates. – Andrew Commented Jan 21 at 15:33
• "Currently stored as int" Well there's your first problem. Fix that (because it really is a broken schema design, and suddenly things get easier. – Joel Coehoorn Commented Jan 21 at 15:46
• So you want to start with a date column, extract the components and store them separately, import the data into Power BI, then reassemble the date column in Power BI? Seems like a lot of work for zero value. – dougp Commented Jan 21 at 15:58
• Or maybe you want to CAST the date value as TIMESTAMP in SQL (Power BI datetime). – Fred Commented Jan 21 at 16:05

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 0

You should be able to use the YEAR and MONTH functions in SQL to create separate columns for year and month, something like this:

SELECT DISTINCT

YEAR(date_column) as YEAR,

MONTH(date_column) as Month

FROM

TABLE