none
Find Date from SQL Table in Analysis Services

    Question

  • Hi,

    How can I find date from SQL Table in Analysis Services. Like I have Thousand of date in a table 2013-08-13 10:38:39.003, 2012-12-01 45:30:01.009....... and more, How can I add only Years, Months and Dates from those data?

    Regards,

    Friday, September 27, 2013 12:55 PM

Answers

All replies

  • The question is not clear, what do you mean add? Maybe you want a date dimension. In this case create it, go to explore solution, dimension, new dimension...

    But pheraps it's better if you explain again your issue..

    Friday, September 27, 2013 1:08 PM
  • thanks !

    In my database I have a Date column, where date and time is saved as above format. Now I want to extract Year, Month Data separably, like I have 2012-12-01 45:30:01.009 now from here I want to get Year = 2012, Month = 12 and Day = 01. 

    Hope you get me now


    • Edited by Tuhin007 Friday, September 27, 2013 1:18 PM
    Friday, September 27, 2013 1:17 PM
  • If you are working in SSAS, create named query, import your table as :

    select columndate, year(columndate), month (columndate), day(columndate)

    If you already got your table in SSAS, rightclick on the table, new calculation and for each calculation chose a name and so: 

    year(columndate)

    month (columndate)

    day(columndate)

    After you can have a dimension splitted for year, month, day.

    Vote as helpful if this post helped you

    • Marked as answer by Tuhin007 Friday, September 27, 2013 2:14 PM
    • Unmarked as answer by Tuhin007 Sunday, September 29, 2013 1:50 PM
    Friday, September 27, 2013 1:57 PM
  • I have Schedule table and Date is a column of the table, which contain Date and time like 2013. 01.10 12:00:13.Now if u plz tell how can I add Year, Month, Day in the cube and/or Dimesion and also how can I fetch/show those in Excel?
    Sunday, September 29, 2013 1:51 PM
  • I have Schedule table and Date is a column of the table, which contain Date and time like 2013. 01.10 12:00:13.Now if u plz tell how can I add Year, Month, Day in the cube and/or Dimesion and also how can I fetch/show those in Excel?

    Hi Tuhin007,

    DIEGOCTN is right. Firstly, if your "Date" table doesn't contain "Year", "Month" columns, we need to create a named calculation for "Date" table in the data source view to get expected values. Then, we can create a dimension by using the "Date" table. I reommend you take a look at the following articles: 
    Define Named Calculations in a Data Source View: http://technet.microsoft.com/en-us/library/ms174859.aspx
    Create a Time Dimension by Generating a Time Table: http://technet.microsoft.com/en-us/library/ms174832.aspx
    Business Intelligence: Building Your First Cube: http://technet.microsoft.com/en-us/magazine/ee677579.aspx

    After that, we can create a SSAS data connection in Excel to create PivotTable or PivotChart for business analysis. For more information, please see:
    Using Excel to interact with a SSAS cube: http://www.mssqltips.com/sqlservertip/2828/using-excel-to-interact-with-a-ssas-cube/

    If you have any feedback on our support, please click here.

    Best Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Tuhin007 Wednesday, October 02, 2013 1:42 PM
    Wednesday, October 02, 2013 6:04 AM
    Moderator
  • Hi Elvis,

    thanks a lot :)

    Wednesday, October 02, 2013 1:42 PM
  • Hi,

    I found something here for your question...

    http://sqlsaga.com/sql-server/how-to-find-year-month-date-from-a-sql-server-date-column/

    https://www.facebook.com/sqlsaga


    hope it helps :)
    Saturday, November 23, 2013 10:04 PM
  • Hi,

    I found something here for your question

    http://sqlsaga.com/sql-server/how-to-find-year-month-date-from-a-sql-server-date-column/

    https://www.facebook.com/sqlsaga


    hope it helps :)
    Saturday, November 23, 2013 10:05 PM