how to remove timestamp from monthend function?

[#1]

Hi all,

Please an some one help me out with the below issue??

In my script I have a row as below:

Monthend(Month)   as expiry_date,

the  expiry date resultant is presently as 31/1/2016 11:59:59 PM but I want to remove the time stamp from the same in the script level or make the timestamp 0,so that my out put comes after reloading as only 31/1/2016.How is this possible?? please can someone help me??

regards,

Sahana

[#2]

thanks for all your valuable responses.:)

ok... actually the reason behind getting time stamp 12:00:00. is because we are calculating values without considering the time stamps of date fields,but from today() function which is used it wasn't working so the question.

[tresesco]

If your expiry_date has a value like: 31/1/2016 11:59:59 PM you can truncate the fractional part using floor() like:

Load

          Floor(expiry_date) as ExpiryDate

Now in the front-end you are having an integer 42400 equivalent to the date 31/1/2016. If you divide this date difference from today() which is equivalent to 41828 it becomes -

(42400-41828)/365

=572/365

=1.56

Now if you use timestamp() on it, you can't expect a 00:00 because of the fractional part of your result. And here it is clear that - that is not because of today(), but because of simple math - division by 365.  Hope this helps.