Hi,

I have stored a date in the UTC format in the baan table... When I applied a query, I got the date in UTC format as follows...

select tccom450.name, tccom450.date from tccom450

Result :
Ram -----2017-11-14 16:27
Bala -----2017-11-14 16:47

But I need to print date as a normal format as follows...
Ram -----2017-11-14
Bala -----2017-11-14

So how can I modify the query "select tccom450.name, tccom450.date from tccom450" to a new one for support the normal date...?
Can anybody assist me is there any function are available in SQL to perform this conversion...?
Thanks in advance
Regards,
Pralash

Hello,
use sprintf$()

Search the Forum, and you'll get a lot of Information and hints.

e.g. : http://www.baanboard.com/baanboard/showthread.php?t=68705&highlight=sprintf%24%28%29

string only.date(10)

only.date = sprintf$("%u(%02d.%02m.%04Y)",tccom450.date)

Yours : only.date = sprintf$("%u(%04Y-%02m-%02d)",tccom450.date)
Should give : YYYY-MM-DD

or formate the utc date in Report field with sprintf$()

Regards

Hi,

As you suggested, I can convert the UTC format date into the normal format by using sprint function.

But I'm not able to count the number of users who are logged into LN by using the following script...

function read.count.table()
{

select tctls903.logg,count(*):counter
from tctls903
group by tctls903.logg
where tctls903._index1 inrange {:logg.f}
and {:logg.t}
selectdo
act.date = sprintf$("%u(%04Y-%02m-%02d)",tctls903.logg)
rprt_send()
endselect
}

I got the normal date in the report by using the variable act.date which one is defined in the input field of the report as follows

2017-11-10 ---- 1 (count)
2017-11-10 ---- 1
2017-11-10 ---- 1
2017-11-10 ---- 1
2017-11-11 ---- 1
2017-11-11 ---- 1

Actually the date is stored in the UTC in the table... So I can get the count for 1 because of minute and sec are differ for each record...
Note:
But I need the count for 4 as on date 2017-11-10... And total count 2 as on date 2017-11-11. So how can I do that...?

Result is expected as follows...
2017-11-10 ---- 4
2017-11-11 ---- 2


Please assist me.
Regards,
Pralash

Hi,

You can do something like this -

domain tcbool first.time
domain tcmcs.long cnt |* For counting
domain tcmcs.str50 prev.date

cnt = 0
first.time = true
prev.date = ""

select tctls903.logg
from tctls903
where tctls903._index1 inrange {:logg.f} and {:logg.t}
order by tctls903.logg
selectdo
act.date = sprintf$("%u(%04Y-%02m-%02d)",tctls903.logg)
if first.time then
first.time = false
prev.date = trim$(act.date)
cnt = cnt + 1
else
if trim$(prev.date) <> trim$(act.date) then
rprt_send()
cnt = 0
else
cnt = cnt +1
endif
endif
selecteos
cnt = cnt + 1
rprt_send()
endselect

Just make sure you print prev.date instead of act.date on your report.

Thanks so much for providing the script....
When I applied this script in my program, it printed the counting for the first date in the table and the rest one in not considered....
I attached the output here. So what can I do for finding the counts for all remaining date?
Please assist.
Regards,
Pralash