Date Conversion Issue

[devrajR]

Hi,

I have a date column from which I derive two new columns: Day and LastDay of the month. The Datatype is $numeric$timestamp. How to I convert it to $numeric$integer? 

I tried using num(Day), but it converts the day to some random 5 digit number. For eg: Day for 22/04/2022 is converted to 44671, due to which I am getting issues in performing arithmetic operations on the day field.

Any suggestions would be helpful.

Thanks in advance,

Devraj

[Or]

 I'm not following as to what your issue is. That number isn't random, it's the numeric value of the date in question.

If you're trying to convert this to some sort of numeric format, you can use e.g.

Year(Date) * 10000 + Month(Date)*100 + Day(Date)

Or whatever other format you want as derived from the original date.

[devrajR]

For 20/04/2022, I need the value of day i.e. 20 in a new column, right now I am getting 44671. How do I get just 20 in a new column. I used Day(20/04/2022) and it gave me 20, but the data type was $numeric$timestamp. I am not able to do arithmetic operations on that.

[devrajR]

I need to get the percent value by dividing the day number and last day of the month.

Date	Day	MonthLastDay	Percent
20/04/2022	20	30	66.67%
21/04/2022	21	30	70.00%
22/04/2022	22	30	73.33%
23/04/2022	23	30	76.67%

[vinieme12]

Use the Day() function

Day(Date) as Day

Day(monthend(Date)) as MonthLastDay

[devrajR]

I have tried the same thing, but for percent when I do (Day / MonthLastDay) it doesn't give me proper values. For 20th April 2022 the calculation that happens in the background is 44671 / 44681 = 0.9997. But I need, 20 / 30 = 0.6667. I think it is because the datatype for Day(Date) and Day(monthend(Date)) is saved as $numeric$timestamp

[Or]

I don't quite follow - why can't you perform an arithmetic operation on the day() value? It's numeric, so shouldn't be a problem.

[hic]

I suggest you read about how the Qlik dates work:
https://community.qlik.com/t5/QlikView-Documents/QlikView-Date-fields/ta-p/1484786
https://community.qlik.com/t5/Design/The-Date-Function/ba-p/1463157

But in short:
* The 44671 isn't random. It is the date serial number. Same as Excel.
* Day(Date) will return 20, for the 20th of the month.
* Day(MonthEnd(Date)) will return the number of days in the month.

So, Day(Date) / Day(MonthEnd(Date)) should do the trick.

[Or]

Load Day(Today()) as Day, Day(MonthEnd(Today())) as MonthEnd, Day(Today()) / Day(MonthEnd(Today())) as Ratio
AutoGenerate(1);

[devrajR · Accepted solution]

Thanks for your help! I was using Day() function then store in a table and then perform operations using num() which was giving the error. Now I have directly done operations using date() on day and monthend values and it works fine. 

Thanks to all who replied! 🙂