?
Solved

dateserial function inside cfquery using MSSQL as opposed to MS Access

Posted on 2007-11-19
3
Medium Priority
?
522 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:Judy Deo
3 Comments
 

Author Comment

by:Judy Deo
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 200 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 53

Accepted Solution

by:
_agx_ earned 1800 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

585 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