How to select a Day from a Date.

[StaHorse]

Hi,

I have this expression "where num(Date(LogTimeStamp)) >= num(monthstart(AddMonths(date(today()),-2)));" I need to change it to look only at only the previous day. please help.

[BrunPierre]

Hi,

Filter for only the previous day data as below.

"where num(Date(LogTimeStamp)) = Num(Date(Today()-1)) ;"

[StaHorse]

Hi,

There's my date:

and when I put this condition "where num(Date(LogTimeStamp)) = Num(Date(Today()-1)) ;" I get nothing back. I expect to get all the data for the 05th, which is a day before today (06 March) 

[BrunPierre]

Try with this.

Where Date(Timestamp#(ErrorTime, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Date(Today()-1, 'YYYY-MM-DD')

[StaHorse]

Hi,

I still get empty results:

Here's my query:

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Date(Timestamp(LogTimeStamp, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Date(Today()-1, 'YYYY-MM-DD')
;

[Or]

Date() does not trim the time from a timestamp. If you want to trim the time aspect, use Floor(Date) or Dayname(Date).

[StaHorse]

I tried both Floor and DayName, still no results:

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Floor(Timestamp(LogTimeStamp, 'YYYY-MM-DD hh:mm:ss'), 'YYYY-MM-DD') = Floor(Today()-1, 'YYYY-MM-DD')

[Or]

That doesn't look like the correct use of Floor()... why did you give it a YYYY-MM-DD parameter? Floor is a mathematical function...

[StaHorse · Accepted solution]

I solved it, I did this instead:

let vCurentMonth = Month(addmonths(today(),-0));
let vPreviousDay = Day(Today()-1);

FailedReloads:
LOAD
Id,
LogTimeStamp as ErrorTime,
Day(LogTimeStamp) as LogDay,
month(LogTimeStamp) as LogMonth,
Hostname as ErrorHost,
"Task Name" as FailedTask,
"App Name" as FailedApp,
EndStatus as Failed,
Message as Message,
rowno() as FailedRow
resident TCheckReloadTasks
where Day(LogTimeStamp) = '$(vPreviousDay)'
and month(LogTimeStamp) = '$(vCurentMonth)'