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

1

• 

  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

  1

  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.

  14

  Comment actions Permalink
• 

  Zachary Rygwelski March 25, 2020 13:38

  I was trying this same thing at the ElastiCube level in version 8.0.2 and there was still not an option to convert a timestamp/datetime.  I would expect there to be something in the table options to convert like I can a number to a string, and also in the custom query options to cast such as DATE(datetime).  Is there something in the Sisense roadmap to include this? Thanks

  0

  Comment actions Permalink
• 

  Kirill Malin April 12, 2020 19:00

  Hi there,

  try this case

  createdate(getyear([DateTime]), getmonth([DateTime]), getday([DateTime]))

  0

  Comment actions Permalink

Please sign in to leave a comment.