http://coldfusion.com logo
Join Slack
Powered by
I need help to be able to download API data using ...
# cfml-beginners
e
I need help to be able to download API data using FROM / TO dates. I want to use +- 1 second as: from 
04/26/2022 00:00:01
to 
04/26/2022 23:59:59
. I can do it in sql (Oracle), but wonder if/how it can be done in CF. Oracle: 
select to_char( trunc(sysdate-1) + (1/(24*60*60)), 'MM/DD/YYYY HH24:MI:SS') from dual;
select to_char( trunc(sysdate) - (1/(24*60*60)), 'MM/DD/YYYY HH24:MI:SS') from dual;
d
s
or if you want to ignore the time in the actual date variable, you can do something like hardcode the time and use dateformat for the date variable. for example I have done things like this to declare variables (in cfquerys against MS SQL Server)
Copy code
<cfquery name="myQuery" dataSource="myDatasource">
DECLARE @startdate datetime = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateformat(startdate,'yyyy/mm/dd')# 00:00:00.000">

DECLARE @enddate datetime = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateformat(enddate,'yyyy/mm/dd')# 23:59:59.999">

select columnA from myTable where dtCreated >= @startdate AND dtCreated <= @enddate
</cfquery>
e
I have no problem doing it in database, but was hoping I can convert Now() to yesterday's midnight +1 sec and today's midnight -1 sec
s
you could probably do it several ways but essentially would use dateadd to get to yesterdays date. Then you could use various ways of creating a datetime string that has the time you want... I find it simplest to just hardcode the start time and end time
Copy code
<cfquery name="myQuery" dataSource="myDatasource">
DECLARE @startdate datetime = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateformat(dateadd('d',-1,now()),'yyyy/mm/dd')# 00:00:00.000">

DECLARE @enddate datetime = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateformat(dateadd('d',-1,now()),'yyyy/mm/dd')# 23:59:59.999">

select columnA from myTable where dtCreated >= @startdate AND dtCreated <= @enddate
</cfquery>
b
Copy code
dateAdd( "s", 1, dateFormat( now(), "short" ) )
dateAdd( "s", 86399, dateFormat( now(), "short" ) )
if your end goal is to do something like Scott's example queries, then you don't really need to check to see if it is between two midnights.... just check to see if its date is "TODAY" by ignoring the times 
WHERE CAST(dtCreated AS DATE) = CAST(GETDATE() AS DATE)
switch to GETUTCDATE() if your dates are all UTC.
all database engines will have the ability to convert a dateTime value to just a date
s
Definitely there are multiple ways to handle this
👍 1
g
i would better do in sql because when you modify these settings in sql and CF and trying to do same thing, the results can be different, i would better create a SPX or a UDF for this and let sql do the work
if i understood your requirement clearly
e
I ended up doing this
<cfquery name="q1" datasource="#REQUEST.T#">
select
to_char( trunc(sysdate-1) + (1/(24*60*60)), 'MM/DD/YYYY HH24:MI:SS') from_utc
,to_char( trunc(sysdate) - (1/(24*60*60)), 'MM/DD/YYYY HH24:MI:SS') to_utc
from dual
</cfquery>
<cfset from_UTC = #dateTimeFormat(dateConvert("Local2UTC", "#q1.from_utc#"),"yyyy-mm-dd'T'HH:nn:ss.lll'Z'")#>
<cfset to_UTC = #dateTimeFormat(dateConvert("Local2UTC", "#<http://q1.to|q1.to>_utc#"),"yyyy-mm-dd'T'HH:nn:ss.lll'Z'")#>
<cfset _URL = "<https://api.returnly.com/returns?min_updated_at=#from_UTC#&max_updated_at=#to_UTC#&page=1&per_page=1000>">
<cfoutput>
fromUTC: #q1.from_utc#<br />
toUTC: #<http://q1.to|q1.to>_utc#<br />
URL: #_URL#
</cfoutput>
4 Views
Open in Slack
PreviousNext
Powered by