topic Re: Date time query in Discussions

Date time query

UserID16644 — Mon, 10 Jul 2023 08:38:34 GMT

Hi all,

I am trying to make a script where I need to query data for the whole day. I have this sample code:

dt = Open Database( Username=username ;Password=password0;APP=JMP;DATABASE=db01;", 
"select *
from  table
where  date_time between '07/10/2023 00:00:00 AM' and '07/10/2023 11:59:00 PM'" );

Is there a way I can put 07/10/2023 00:00:00 AM and 07/10/2023 11:59:00 PM in a variable?

I tried using this:

i = As Date( Today() );
dateToday = MDYHMS(i);

But it is only showing the hour it was run. Please help

Re: Date time query

jthi — Mon, 10 Jul 2023 08:59:12 GMT

Do you want to get the date based on todays date? If you just want to have string in a variable, you can just assign in

start_time = "07/10/2023 00:00:00 AM"

and then concatenate it to sql query or maybe better would be to use Eval Insert

Re: Date time query

UserID16644 — Mon, 10 Jul 2023 23:19:28 GMT

Yes, I need the date today but not hardcoded that's why I tried using

i = As Date( Today() );
dateToday = MDYHMS(i);

But the time it gives is only the time it run, not from 00:00:00 AM - 11:59:59 PM

Re: Date time query

jthi — Tue, 11 Jul 2023 07:02:19 GMT

Datetime values are handled internally as numbers of seconds since midnight, January 1, 1904. So you can deduct and add some seconds as needed

Names Default To Here(1);

now = As Date(Today());
start_time = MDYHMS(now - Time Of Day(now)); // "07.11.2023 0:00:00"
end_time = MDYHMS(now - Time Of Day(now) + In Days(1) - 1); // "07.11.2023 23:59:59"