656826_en-US

liliconcom

Date time query

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

 

Automation and ScriptingWindowsRe: Date time query

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

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

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"