Search code examples
sql-server-2008datedatetimesplitmondrian

SQL Server split datetime column

I'm trying to split a datetime column in my SQL Server database. All entries are in the format: yyyy-mm-dd 00:00:00.0 (yes, every time dimension is uselessly the same, but there's nothing I can do about it, it's not my db).

Actually, what I really want to do is to create a new time dimension with 4 columns: Date, Year, Month and Day (that's because I need to form a Mondrian hierarchy based on that time dimension).

I really don't know how to to this, any help would be appreciated.

Solution 
  • select DateTimeCol, DATEPART(YEAR, DateTimeCol) as YearCol, DATEPART(MONTH, DateTimeCol) as MonthCol, DATEPART(DAY, DateTimeCol) as DayCol 
into NewTable
from OldTable