SQL TRUNC style function in Qlikview

[#1]

Hi Folks, I am looking for SQL TRUNC style function to truncate the time stamp values.

Thanks in Advance...

[disqr_rm]

floor() ?

[rajeshvaswani77]

Hi Dathu,

Please use Yearstart function.

yearstart ( '2001-10-19' ) returns '2001-01-01'

yearstart ( '2001-10-19', -1 ) returns '2000-01-01'

yearstart ( '2001-10-19', 0, 4 ) returns '2001-04-01'

thanks,

Rajesh Vaswani

[tresesco]

Like TRUNC with different formats (YEAR, Q, MONTH....), you won't get all in one in QV, but you can try separately,

YearStart(), MonthStart(), WeekStart() functions.

[pgrenier]

Good morning Dathu,

I would invite you to use two functions together Date() and Floor(). Using Floor() on a date will truncate the hours, minutes, seconds and miliseconds from the date, but will also convert it to a numerical value. Hence, the need for wrapping it with the Date() function to bring it back to a date format.

LOAD Date(Floor(TIMESTAMP_FIELD)) as Date_Field

From ...

Regards,

Philippe

[#6]

Thanks for reply's. But specifically looking how to truncate the timestamp value to hours and minutes.

TRUNC(TIMEFIELD,'HH24')  and TRUNC (TIMEFIELD,'MI')

[tresesco]

try:

Timestamp(TIMEFIELD, 'hh')  , and

Timestamp(TIMEFIELD, 'mm')

[pgrenier · Accepted solution]

Otherwise, if you are looking to keep the date while eliminating the seconds, your could either use the following expression:

Date(Floor(TIMEFIELD) + Hour(TIMEFIELD)/24 + Minute(TIMEFIELD)/(24*60), 'YYYY-MM-DD hh:mm:ss')

or

Date(Floor(TIMEFIELD, 1/(24*60)), 'YYYY-MM-DD hh:mm:ss')

There is a possibility to define the base for the floor fonction, so if to define the base to 1/(24*60), it shall truncate the seconds. In the same manner, using the 1/24 base shall truncate your dates to the hours, and going for 1/(24*60*60) will eliminate the milliseconds.

Hope this helps,

Philippe

[#9]

I tried this one in script level. But this will give full timestamp whenever I can use the Timestamp function.

So I used this one : Timestamp#(text(timestamp(TIMEFIELD,'MM/DD/YYYY h:mm')))

But it kills the time because I am working on very lagre dataset like 16M rows.

[orital81]

Try :

Hour(TIMEFIELD)  , and Minute(TIMEFIELD)