how to get the last second on a given year

[#1]

Hi,

I need to calculate the last second of  a given year and represent it as a full date+time dd/mm/yyyy hh:mm:ss

in order to compare this date+time to other given date+time in my model...

how can i create a full date+time value from a given year...

for example:

given year: 2005

i neet to change it to 31/12/2005 23:59:59

Thank you.

[martinpohl]

like this

Regards

[sunny_talwar]

May be this:

=Timestamp(YearEnd(MakeDate(2015)))

Replace MakeDate(2015) with your Date Field or leave it static based on your requirement

[Colin-Albert]

The YearEnd() function will return the value you need.

YearEnd( date [, shift = 0 [, first_month_of_year = 1]])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the year containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the year which contains date. Negative values in shift indicate preceding years and positive values indicate succeeding years. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

[swuehl]

Take care that YearEnd returns the last millisecond of the year,even when formatted only up to seconds.

Just in case you are perfoming a match with a real second based timestamp.

edit:

Timestamp#(Timestamp(YearEnd(MakeDate(2015))))

[#6]

=Timestamp(MakeDate(2005+1) - 1/86400)

Subtracts one second from the next year and therefore gives the last second of this year.

[#7]

Thank you all

[swuehl]

Nice idea, Do Kra!

I just want to emphasize that comparing floating point values, like Timestamps from a source and timestamp values created in the Qlik script is error prone (though not sure if the OP is really comparing floating point values):

Correct Time Arithmetic | Qlikview Cookbook‌

Hence it's good practice to use the same kind of number (re-)generation on both sides.