Custom Column - Get Date from Datetime format
AnsweredDear 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
-
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 -
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.
-
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
Please sign in to leave a comment.
Comments
7 comments