Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


dateserial function inside cfquery using MSSQL as opposed to MS Access

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


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'
(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)

Open in new window

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

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.

Assisted Solution

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




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

Accepted Solution

_agx_ earned 1800 total points
ID: 20313721

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 
SELECT 	convert(datetime, sdyear + sdmonth + sdday, 112) AS sdDateTime,
	request_status, contact_ln, contact_fn
	-- include any other columns here
FROM    tableName
--- query
FROM    DateTimeViewOfYourTable
  sdDateTime between <cfqueryparam cfsqltype="cf_sql_date" value="#user_startdate#"> and 
        <cfqueryparam cfsqltype="cf_sql_date" value="#user_enddate#"> 
  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'
  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


Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

609 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