Problem Using Query To Get Records Between Two Dates
Feb 10, 2008
I´m trying to select records which are between two dates. I use the following statement.
qry = System:tring::Format("SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr="+tbStudentId->Text+" and dateofmeal >=#"+dt->ToString("dd/MM/yyyy 00:00:00" )+"# and dateofmeal <=#"+dt2->ToString("dd/MM/yyyy 00:00:00" )+"#" );
Although the records exists the query does not get these records. If I go to the Query Design and use the same query it works but only if I enter the dates manually (dateofmel >=?).
I have a SQL query I need to design to select name and email addressesfor policies that are due and not renewed in a given time period. Theproblem is, the database keeps the information for every renewal inthe history of the policyholder.The information is in 2 tables, policy and customer, which share thecustid data. The polno changes with every renewal Renewals in 2004would be D, 2005 S, and 2006 L. polexpdates for a given customer couldbe 2007-03-21, 2006-03-21, 2005-03-21, and 2004-09-21, with polno of1234 (original policy), 1234D (renewal in 2004), 1234S (renewal in2005), and 1235L (renewed in 2006).The policy is identified in trantype as either 'rwl' for renewal, or'nbs' for new business.The policies would have poleffdates of 2004-03-21 (original 6 monthpolicy) 2004-09-21 (first 6 month renewal) , 2005-03-21 (2nd renewal,1 year), 2006-03-21(3rd renewal, 1 yr).I want ONLY THE LATEST information, and keep getting earlyinformation.My current query structure is:select c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%s'and p.trantype like 'rwl'and c.email is not nullunionselect c.lastname, c.email, p.polno, p.polexpdatefrom policy p, customer cwhere p.polid = c.polidand p.polexpdate between '2006-03-01 and 2006-03-31and p.polno like '1234%'and p.trantype like 'nbs'and c.email is not nullHow do I make this query give me ONLY the polno 123%, or 123%Sinformation, and not give me the information on policies that ALSOhave 123%L policies, and/ or renewal dates after 2006-03-31?Adding a 'and not polexpdate > 2006-03-31' does not work.I am working with SQL SERVER 2003. Was using SQL Server 7, but foundit was too restrictive, and I had a valid 2003 licence, so I upgraded,and still could not do it (after updating the syntax - things likeusing single quotes instead of double, etc)I keep getting those policies that were due in the stated range andHAVE been renewed as well as those which have not. I need to get onlythose which have NOT been renewed, and I cannot modify the database inany way.*** Free account sponsored by SecureIX.com ****** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.
The asp.net code I am trying to use is:
Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"
....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim startdate As DateTime startdate = Request.Form("date_from")
Dim enddate As DateTime enddate = Request.Form("date_to")
I have a database table which contains customer orders. I am trying to code my SQL select statement to:
1) Only return records where the record orderdate is within the last 30 days
2) Between two dates, selected from the datepicker control.
With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.
I am trying to create a report where the rows of the database have fields containing customer names and dates associated with the names. I want to know for each customer how many records the customer has for specified date ranges.
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate Received1] FROM [DL] WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#)) GROUP BY [DL].[Customer];
The idea is a list of the number of records for each customer in 2013.
Ultimately my goal is to show the customer activity over multiple years.
So, Customer Name 201120122013 Customer A123 Customer B246 Customer C543
I am trying to go down the path of a Union:
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate 2013] FROM [DL] WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#)) GROUP BY [DL].[Customer]
[Code] ....
This returns 2 columns only not the four I am looking for.
I am trying to pull only those records with a maximum upload date for a file upload log.
This table keeps a list of files uploaded by supplierID and will have multiple records from the same supplier but with all different upload dates. Overall there are over 1,000 records for 48 uniqur suppliers.
The field names are: SupplierID, UploadDate, FeedFileName, RecordCount, FeedFileDate, RecordCount, and FeedLoaded.
So for each distinct SupplierID I'd like to grab the line item based on the max feedfiledate for each one - in other words I am trying to get the latest uploaded file information, how many records were in the file, and when it was uploaded... I've tried multiple group by and max clauses but there is something I am missing...
Hi, I was wondering if anyone can help me write a SELECT statement to return all records (rows) based on whether they fall within two dates. For example, consider this table:
Code Snippet --DROP TABLE #Records CREATE TABLE #Records (RecordID INT PRIMARY KEY NOT NULL, StartDate DATETIME, EndDate DATETIME) INSERT INTO #Records VALUES (1, '2008-05-01', '2008-05-12') INSERT INTO #Records VALUES (2, '2008-05-08', '2008-05-12') INSERT INTO #Records VALUES (3, '2008-05-19', '2008-05-22') INSERT INTO #Records VALUES (4, '2008-05-22', '2008-05-23') INSERT INTO #Records VALUES (5, '2008-05-26', '2008-06-01') INSERT INTO #Records VALUES (6, '2008-05-28', '2008-06-01') SELECT * FROM #Records
I want to return the RecordID for any row which span this week. I've tried the simple approach of using WHERE GETDATE() BETWEEN StartDate AND EndDate but this doesn't produce the desired results. For example, RecordID 4 spans this current week but is not returned (obviously) with this approach.
The idea is to show which events (rows) are coming up this week and next week. Eventually the data will be used to populate an SSRS report.
Hello. I need to create a page with asp 3 and sql server 2000 that lists all the records in a table that match a date criterion, but when doing so I get an error "Error Converting datetime from character string"... I've tried this versions of the query:
Code:
SELECT (...) CONVERT(DATETIME,PAYMENTS_RECEIVED.PYR_DATE,103), (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE BETWEEN '20/03/2006' AND '21/03/2006')
and...
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME,'20/03/2006',103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME,'21/03/2006',103))
I also tried adding " 00:00:00 a.m." to the dates to be converted in the second piece of code. If I recall correctly, "103" is the convertion code meaning "dd/mm/yyyy". That is, I expect people to type in a date like "27/03/2006" and get the table records from that date.
Weirdest thing of all is that, when using the query builder of a sqlsource control in Visual Studio Web Developer Express 2005 this following query works just fine as I expect:
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME, '20/03/2006', 103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME, ' 21 / 03 / 2006 ', 103))
Both the server and the DB are the same in all cases, but in the first two I run the query from a (working of other queries) vbscript asp 3 page.
I know that similar posts exist already (I've searched for this before posting), but I can't fix it, at least not with the usual answer on this one (that seems to be using the convert function as in the first piece of code). Thanks in advance!!!
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10], MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10], MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11], MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11], MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint
How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.
WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()
Hi, im trying to write a sql query to check if dates are in the db, ias it is it is telling me the dates are there and their not. any help would be great. George
here's what i have...
SQL2 = "SELECT * FROM reservations WHERE" _ & "(arvdate >=#" & request("arvdate") & "# AND endate <= #" & request("endate") & "#) " _ & "OR " _ & "(arvdate >=#" & request("arvdate") & "# AND endate >= #" & request("endate") & "#) " _ & " AND idrent = " & idrent _ & " AND confirmation = 1" _ & " ORDER BY arvdate"
The Sales table includes: date, description, price, consigner The Consignment table includes: lastday, consigner
I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.
I have this and it isn't working:
SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1 FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1 WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));
This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear FROM dbo.Orders_Retail WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded') GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate) ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
etc etc for each week in a year and then it goes onto the next year.
What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.
I've tried to do a WHERE DATEPART(wk, OrderDate) > @StartDate AND DATEPART(wk, OrderDate) < @EndDate AND YEar(OrderDate) > @StartYear AND YEAR(OrderDate) < @EndYear
But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.
I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.
Hello, I have a sql query that I am requireing to get records on date parameters : SELECT * FROM table WHERE StartDate >= 2007/09/30 07:00:00 AND EndDate <= 2007/09/30 09:00:00 I have records with StartDate 2007/09/30 08:00:00, 2007/09/30 08:30:00 which this query does not select. I am trying to select all StartDate which fall between 2007/09/30 07:00:00 AND 2007/09/30 09:00:00. Any suggestions on the query design
I have figured out how to work with dates in SQL, but don't know how to put it all together to completea sql query I am working on. Here are some examples of the basic MS SQL date routines I know: SELECT DATEDIFF(day, '11-2-2007 11:11:11', '12-4-2007 11:11:11') -- Difference between two dates. SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] -- Get's today's date Now back to my problem....I have the following Query that must be modified so that the filter criteria is based upon the"StartDate" being greater than today's date. Can someone please help me out. SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable Note that the date passed in for Start date will be in a format like the following '11-2-2007 09:1:11'. So if I were to pseudo code out the query it would be something like the following, where the pseudo codeis in parenthesis. SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable (WHERE StartDate > Todays Date)
Hi., Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period. Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria This results in the follwoing SQL Statement: strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005 Regards. Peter
i would like to create an SQL query, part of which involves comparing dates.
i have something like
Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....
how can i compare the StartDate to something i have in the form of a string? for example, i have Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN Products ON OrderItems.ProductID = Products.ProductID WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND (Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND (Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid' GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
I am trying to build a VBA Sub that uses a MS SQL query, this is fine it is just I am having trouble building the query to do what I need.
I need to be able to do a between month and years, so if I have a VBA routine that constructs a query from variables. Those variable will be a month and a year.
so I can return a count of all records that were logged between the month and year.
My poorly constructed query is below.
SELECT [DateRaised] FROM HD_Call WHERE DateRaised BETWEEN DATEADD([MONTH], -1, GETDATE()) AND DATEADD([MONTH],0, GETDATE()) AND DateRaised BETWEEN DATEADD([YEAR], -2, GETDATE()) AND DATEADD([YEAR],0, GETDATE()) AND [completed] = 1 AND [Assignee] LIKE '%' AND [CallType] LIKE '%' Order By 1
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this 2-1-2006 3-1-2006 4-1-2006 . . . 16-03-2006 17-03-2006 18-03-2006
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates. (e.g. 1 May 2001 and 20 May 2001) And I would like to select those records that are between two times. (e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me? I can see it would be easier if I had the date in one column and the time in the other. Can it be done without doing that?
