Judy Deo
asked on
dateserial function inside cfquery using MSSQL as opposed to MS Access
Hi, I posted a question a while back (see link below)
https://www.experts-exchange.com/questions/21114405/records-between-two-user-defined-dates.html?sfQueryTermInfo=1+between+date+dateseri
Got an excellent answer that worked nicely. But now we have changed from using MS Acess to MS SQL.
How do I change this code (see attached code snippet) so that MS SQL accepts it. It doesn't seem to like dateserial and possibly cfqueryparam..
https://www.experts-exchange.com/questions/21114405/records-between-two-user-defined-dates.html?sfQueryTermInfo=1+between+date+dateseri
Got an excellent answer that worked nicely. But now we have changed from using MS Acess to MS SQL.
How do I change this code (see attached code snippet) so that MS SQL accepts it. It doesn't seem to like dateserial and possibly cfqueryparam..
<cfquery name="GetAbsenseReportEntries" datasource="dsname" dbtype="ODBC">
select * FROM tablename
WHERE (dateserial(sdyear,sdmonth,sdday) between <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#"> OR dateserial(edyear,edmonth,edday) between <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#">)
AND request_status = 'Approved By HR'
OR
(dateserial(sdyear,sdmonth,sdday) <= <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#">
AND dateserial(edyear,edmonth,edday) >= <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#">
AND request_status = 'Approved By HR'
)
ORDER BY contact_ln, contact_fn, cInt(sdyear), cInt(sdmonth), cInt(sdday)
</cfquery>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER