472,986 Members | 2,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 software developers and data experts.

Need help with CONVERT function

I have a situation where I want to filter records for a given day. The
field that stores the date/time uses the date() function as a default value,
kind of a date/time stamp for the record.

The problem is when I want to filter records for a given day instead of a
date range. I use the CONVERT function to return just the date part of the
field (101 as a style parameter) and compare that to a start and stop date
(both being the same) and I get nothing. The stored procedure is as

Alter Procedure spESEnrollmentCount
@StartDate smalldatetime, @StopDate smalldatetime
SELECT tblCustomers.CustomerName, tblCostCenters.CostCenter,
AS [Count of Enrollments]
FROM tblESEnrollments
INNER JOIN tblCustomers ON tblESEnrollments.CustID = tblCustomers.CustID
INNER JOIN tblCostCenters ON tblCustomers.CostCenterID =
WHERE ( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) >= @StartDate) AND
( CONVERT(DATETIME, tblESEnrollments.DTStamp, 101) <= @StopDate) AND
(Rejected = 0)
GROUP BY tblCustomers.CustomerName, tblCostCenters.CostCenter

If I put 10/31/06 in for both parameters shouldn't I get records dated
10/31/06 if there are some?


Feb 28 '07 #1
1 5155
In order to drop the time portion of a date via the CONVERT function you
have to do a double convert, first to character type and then to datetime.
Here is how it will look:

WHERE ( CONVERT(DATETIME, CONVERT(CHAR(10), tblESEnrollments.DTStamp, 101))
>= @StartDate) AND
101)) <= @StopDate)

However, a better approach is to use the DATEDIFF function as it will be
more efficient than converting and it will allow you to utilize any indexes
on the DTStamp column. Here is how it will look:

WHERE tblESEnrollments.DTStamp >= DATEDIFF(day, 0, @StartDate) AND
tblESEnrollments.DTStamp <= DATEDIFF(day, -1, @StopDate)


Plamen Ratchev

Feb 28 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Google Jenny | last post by:
Precisely, here's what I need: When I run getdate(), I get, for example: August 9 2004 5:17 P.M. I want to turn the date portion into: 8/9/2004 format and update one column with it
by: Timothy Shih | last post by:
Hi, I am trying to figure out how to use unmanaged code using P/Invoke. I wrote a simple function which takes in 2 buffers (one a byte buffer, one a char buffer) and copies the contents of the byte...
by: Ewart MacLucas | last post by:
generated some WMI managed classes using the downloadable extensions for vs2003 from mircrosoft downloads; wrote some test code to enumerate the physicall processors and it works a treat, but a...
by: Glenn Wilson | last post by:
I have converted most of the code that I have but am having trouble, mainly with the marked lines. (>>) public static UInt16 checksum( UInt16 buffer, int size ) { Int32 cksum = 0; int counter;...
by: patang | last post by:
Could someone please tell me where am I supposed to put this code. Actually my project has two forms. I created a new module and have put the following code sent by someone. All the function...
by: Charlie Brookhart | last post by:
I have a program (posted below) that is supposed to take liters, which is the user input, and convert it to pints and gallons. The pints and gallons are displayed in a read only textbox. I don't...
by: Learner | last post by:
Hello, Here is the code snippet I got strucked at. I am unable to convert the below line of code to its equavalent vb.net code. could some one please help me with this? static public...
by: williamvarah | last post by:
I want to be able to link a macro to an icon in excel so that I can run a function that I have in excel visual basic. I'm trying to use runcode to do this but it's not working. The code for the...
by: KW | last post by:
I have an application that runs in any of Access 2000 thru Access 2007 to accommodate my customers' various environments. I implemented code to call a function that uses the LoadCustomUI method to...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.