dateTime in select query

[Dev]

Hi,

I need to sum up a value with date,time criteria. Since date and time are
different fields, how to give a datetime in the search.

for example

i need to get the count of login users from yesterday (10/14/2007) from
10:30 am to today (10/15/2007) 12:30 pm

anybody please help me to pass the date&time as one criteria

like logindate >='10/14/2007 10:30:00' && logindate >='10/15/2007 2:30:00'

thanks in advance
Dev

[Mathias]

Hi Dev,

you need to use a complex criteria.
I might look like this

q.dataSourceTable(tablenum(Sysuserlog)).addRange(fieldnum(SysUserLog,
RecID)).value(strfmt('( ((%1 == %2) && (%3 >= %4)) || ((%1 == %5) && (%3 <=
%6)))',
fieldstr(SysUserLOg, createdDate),
date2strxpp(mkdate(10,01,2007)),
fieldstr(SysUserLog, createdTime),
str2time("10:30:00"),
date2strxpp(mkdate(11,01,2007)),
str2time("18:30:00")));

for further information have a look at
http://www.axaptapedia.com/Expressions_in_query_ranges

I hope this will help

Regards
--
Mathias Füßler
my blog: http://starside.eu

[Mathias]

On second thoughts the range will more look like this

q.dataSourceTable(tablenum(Sysuserlog)).addRange(fieldnum(SysUserLog,
RecID)).value(strfmt('(((%1 > %2) && (%1 < %5)) || ((%1 == %2) && (%3 >= %4))

|| ((%1 == %5) && (%3 <= %6)))',
fieldstr(SysUserLOg, createdDate),
date2strxpp(mkdate(10,01,2007)),
fieldstr(SysUserLog, createdTime),
str2time("10:30:00"),

date2strxpp(mkdate(15,01,2007)),
str2time("12:30:00")));

((%1 == %2) && (%3 >= %4)) = all records on startdate after starttime
((%1 == %5) && (%3 <= %6)) = all records on enddate before endtime
((%1 > %2) && (%1 < %5)) = all records between startdate and enddate

--
Mathias Füßler
my blog: http://starside.eu