Custom Column - Get Date from Datetime format

Answered

Follow

Nishya Sathianesan

March 27, 2019 12:41

Dear all,

I have a column which in Date time format (eg. 27.03.2019 13:39:00). How can create a custom column in the cube where I can have only the date(eg. 27.03.2019)? Thank you

0

• 

  Andrew Block March 27, 2019 13:38

  Nishya,

  
  Prodvided you wanted to display just as you presented it would be:

  LEFT([Date],10)

  

  Or if you wanted an actual Date field out of that you could use the following:

  CreateDate(TOINT(RIGHT(LEFT([Date],10),4)),TOINT(RIGHT(LEFT(Date,5),2)),TOINT(LEFT([Date],2)))

  

  This is just something I put together quick there may be a more elegant solution 

  
  Let me know if you have any questions

  0

  Comment actions Permalink
• 

  Nishya Sathianesan March 27, 2019 14:14

  Andrew,

  Thanks for the answer. Isn't CreateDate(TOINT(RIGHT(LEFT([Date],10),4)),TOINT(RIGHT(LEFT(Date,5),2)),TOINT(LEFT([Date],2))) again giving the output in Date time format? And if i am not wrong LEFT([Date],10) is string format? I want the output of  LEFT([Date],10) but in Date format not as string. Thank you.

  0

  Comment actions Permalink
• 

  Andrew Block March 27, 2019 14:22

  Nishya,

  Technically what you provided is a string representation of a date time format from some source system I am assuming. I believe Sisense only allows date to be formatted as (year,month,day), you could however just use it as as string.

  0

  Comment actions Permalink
• 

  Andrew Block March 27, 2019 14:27

  Using my second option to convert it an actual Date field in the ECM, you can then format it in dashboard how you want such as 

  

  2

  Comment actions Permalink
• 

  Daniel Cook August 06, 2019 17:05

  Why isn't there simply a ToDate(datetime) function, or the ability to CAST(datetime as date)? It's a very very common scenario.

  9

  Comment actions Permalink

Please sign in to leave a comment.