locked
Get year from Timestamp RRS feed

  • Question

  • Hello,

    I've a column with name DateTimeStamp the data of this column looks like

    DateTimeStamp

    2011-11-29 11:04:25.340000000

    2012-11-29 11:04:25.340000000

     

    Now i want to retrieve only year from this. To achieve this i'm using the DAX as Year([DateTimeStamp]) but i'm get error as "cannot convert value of type string '2011-11-29 11:04:25.340000000' to type date "

    How can i overcome this.

    Tuesday, June 5, 2012 12:44 PM

Answers

  • Ok, I tested it with your sample values and it seems, there is a problem with the millisecond part. If you truncate the ms, then it works.

    Here is a screenshot to see howto: First truncate with the LEFT function to 19 chars = without ms, then you can use YEAR with this new calulated column.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Jagannalla Wednesday, June 6, 2012 8:12 AM
    Tuesday, June 5, 2012 2:58 PM
  • Try

    =LEFT([CaptureDateTime],4)*1

    • Marked as answer by Jagannalla Wednesday, June 6, 2012 8:12 AM
    Tuesday, June 5, 2012 7:57 PM

All replies

  • Hello,

    In common this works, even if the datatype of the source column is a text (string), as long as it contains value date values regarding your local regional settings.

    You should try to change the datatype of the source column from "text" to "date" to see if this works.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Tuesday, June 5, 2012 1:00 PM
  • Olaf Helper,

    I'm new to powerpivot please help me on this. Here i'm attaching my screeshot plz check once.

    Tuesday, June 5, 2012 2:40 PM
  • Ok, I tested it with your sample values and it seems, there is a problem with the millisecond part. If you truncate the ms, then it works.

    Here is a screenshot to see howto: First truncate with the LEFT function to 19 chars = without ms, then you can use YEAR with this new calulated column.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Jagannalla Wednesday, June 6, 2012 8:12 AM
    Tuesday, June 5, 2012 2:58 PM
  • Try

    =LEFT([CaptureDateTime],4)*1

    • Marked as answer by Jagannalla Wednesday, June 6, 2012 8:12 AM
    Tuesday, June 5, 2012 7:57 PM
  • Try

    =LEFT([CaptureDateTime],4)*1

    Indeed, a more smarter solution :-)

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, June 6, 2012 4:54 AM
  • Thanks to help me. 
    Wednesday, June 6, 2012 8:12 AM