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
follows:
Alter Procedure spESEnrollmentC ount
@StartDate smalldatetime, @StopDate smalldatetime
As
SELECT tblCustomers.Cu stomerName, tblCostCenters. CostCenter,
COUNT(tblESEnro llments.Enrollm entID)
AS [Count of Enrollments]
FROM tblESEnrollment s
INNER JOIN tblCustomers ON tblESEnrollment s.CustID = tblCustomers.Cu stID
INNER JOIN tblCostCenters ON tblCustomers.Co stCenterID =
tblCostCenters. CostCenterID
WHERE ( CONVERT(DATETIM E, tblESEnrollment s.DTStamp, 101) >= @StartDate) AND
( CONVERT(DATETIM E, tblESEnrollment s.DTStamp, 101) <= @StopDate) AND
(Rejected = 0)
GROUP BY tblCustomers.Cu stomerName, 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?
Thanks,
Wes 1 5231
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(DATETIM E, CONVERT(CHAR(10 ), tblESEnrollment s.DTStamp, 101))
>= @StartDate) AND
( CONVERT(DATETIM E, CONVERT(CHAR(10 ), tblESEnrollment s.DTStamp,
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 tblESEnrollment s.DTStamp >= DATEDIFF(day, 0, @StartDate) AND
tblESEnrollment s.DTStamp <= DATEDIFF(day, -1, @StopDate)
HTH,
Plamen Ratchev http://www.SQLStudio.com 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 buffer into the character
pointer. The code looks like the following:
#include <stdio.h>
#include <stdlib.h>
#include "stdafx.h"
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
|
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 question..
The code fails with the error that:
"Additional information: COM object that has been separated from its
underlying RCW can not be used." if I make a call to pc.Count before
iterating though the objects.
Dim d As New...
|
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;
counter = 0;
while ( size > 0 ) {
UInt16 val = buffer;
|
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 declaration statments (first lines)
e.g.
Public Function ConvertCurrencyToEnglish(ByVal MyNumber As Double) As String
Private Function ConvertHundreds(ByVal MyNumber As String) As String
etc.
| |
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 understand how to fix the
problem.
'declaring variables
Dim totalLiters As Double = Convert.ToDouble(txtLiters.Text)
Dim totalPints As Double = Convert.ToDouble(txtPints.Text)
|
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 List<RoleData> GetRoles()
{
return GetRoles(null, false);
}
|
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 function is as follows:
Function ConvertCurrencyToUK(ByVal MyNumber)
Dim Temp
Dim Pounds, Pence
Dim DecimalPlace, count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
|
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 load in my
custom ribbon for Access 2007. This function is called only when the
check for running Access version 12 or higher is true.
Everything works like I want in Access 2007. The problem is when I
compile it on Access 2000,2002,2003,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |