Solved

dateserial function inside cfquery using MSSQL as opposed to MS Access

Posted on 2007-11-19
3
510 Views
Last Modified: 2013-12-24
Hi, I posted a question a while back (see link below)

http://www.experts-exchange.com/Software/Server_Software/Web_Servers/ColdFusion/Q_21114405.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>

Open in new window

0
Comment
Question by:sagardeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Author Comment

by:sagardeo
ID: 20313462
oh and really nice how experts-exhcange allows you enter code snippets separately now. this site keeps getting more and more organized and much better.
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 50 total points
ID: 20313589
Some nice TSQL functions you can use here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

http://www.webbedwonder.com/SQL-Server/Access-queries-to-TSQL.asp

http://www.dbforums.com/showthread.php?threadid=883255

I'll work on a complete solution when I get a little more time.. sorry but work is blowing up at the moment :/
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 450 total points
ID: 20313721
sagardeo,

Another option is to create a reusable ms sql "view" that converts the year,month,day to a datetime. Then you can use the datetime column in the "view" just as if it were a column in your table. It will save you from having to run the convert function in every query.

Two notes, since you're using CInt() on the year,month,day I am assuming those are varchar/text columns. Also, are you sure the grouping of the AND/OR's is correct?
--- create the "view" once, then you can re-use it anywhere
CREATE VIEW DateTimeViewOfYourTable 
AS
SELECT 	convert(datetime, sdyear + sdmonth + sdday, 112) AS sdDateTime,
	request_status, contact_ln, contact_fn
	-- include any other columns here
FROM    tableName
 
 
--- query
SELECT  *
FROM    DateTimeViewOfYourTable
WHERE ( 
 
  sdDateTime between <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#"> and 
        <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#"> 
OR 
  sdDateTime 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 
(
  sdDateTime <= <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#"> and
  sdDateTime >= <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#"> and
  request_status = 'Approved By HR' 
)
ORDER BY contact_ln, contact_fn, sdDateTime

Open in new window

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question