Solved

dateserial function inside cfquery using MSSQL as opposed to MS Access

Posted on 2007-11-19
3
499 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
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now