I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
SELECT *
FROMtbl_Feedback
WHERE tbl_Feedback.DateIn >= @startDate
GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead
' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command")
set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput)
set cmd.ActiveConnection=DBConnection
cmd.CommandText="sp_009_SiteLead"
cmd.Parameters.Append peDate
cmd.Parameters.Append psdate
psDate.Value = "10/01/2004"
Set rsObj = CreateObject("ADODB.Recordset")
rsObj.ActiveConnection = DBConnection
rsObj.CursorLocation = adUseClient
rsObj.PageSize= 20
rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it.
Can anyone help?
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck 2 @PID INTEGER = -1 OUTPUT, 3 @PCityFrom Char(3) = '', 4 @PCityTo Char(3) = '', 5 @PDateFrom DATETIME = '31 Dec 9999', 6 @PDateTo DATETIME = '31 Dec 9999', 7 @PIsExists BIT = 1 OUTPUT 8 AS 9 10 CREATE TABLE #TmpControlRequst 11 ( 12 IDINTEGER, 13 IsExistsCHAR(1) 14 ) 15 /*###Pseudo 16 1. Check the Date From and Date To 17 -- select all the value equal to parameter cityFrom and cityTo 18 -- insert the selection records into tmp table 19 --*/ 20 INSERT INTO #TmpControlRequst 21 (ID, IsExists) 22 SELECT ID, 23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1 29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo 31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999' 33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo 34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1 35 ELSE 0 END 36 FROM RequestTable 37 WHERE ID <> @PID 38 AND CityFrom = @PCityFrom 39 AND CityTo = @PCityTo 40 41 --======== FINAL RESULT 42 -- For tmp table:- 43 -- isExists = 1 ==> date lapse 44 -- isExists = 0 ==> date ok 45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting 46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1 47 ELSE 0 END 48 FROM #TmpControlRequst 49 WHEREIsExists = 1 50 51 SELECT @PIsExists 52 --========= 53 54 DROP TABLE #TmpControlRequst 55 56 --========= 57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
What is the recommended method of returning a formatted date from a stored procedure?
The date is held in a date time field. I wish to return the date formatted as:
dd/mm/yyyy hh:mm
for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:
dd/mm/yyyy hh:mm:ss
I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.
I was looking at:
select jobHeaders.DateTimeJobTaken AS [Job Taken], CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' + CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))
from jobHeaders
but this gives : 8 /3 /2004 with spaces.
Before looking further I thought one of you guys may have the answer.
I have stored procedure which runs on some period, what i want is when it first run i want to store that date permanently or till the next time it runs again, and then i need to take my data from that store proc , where hist_date between (date stored when it ran first time,example, 08/21/2015) and today date.(exampple, 08/24/2015)
Now next time when it runs , date stored should be updated in this case it should be (08/24/2015) .
How can i do this in stored proc, I tried to use temp table but it didn't work .
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name] ,p.designer,p.weight,p.price from productdescription pd inner join products p on pd.productcategory=p.productcategory where @prodcat=p.productcategory order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns) 22 47 22 47 22 58 22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen. So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info. Any idea y? They both should be bringing back information from the same table not one from my live DB and one from my TEST DB. Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows: CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement: SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
Date parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
Date Temperature 09/01/2012 73.5 09/02/2012 75.2 09/03/2012 76.3 09/04/2012 73.3 09/05/2012 77.0 09/06/2012 74.5 and so on.
I am using this to get the dates listed: WITH CTE_DatesTable AS ( SELECT CAST('20120901' as date) AS [Date] UNION ALL SELECT DATEADD(dd, 1, [Date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [Date]) <= '20120930' ) SELECT [Date] FROM CTE_DatesTable
How could I get the temperature if I did a sub-query here?
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date. so... WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2) OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out. WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this WHERE reportdaye between 2/1/2007 and 1/30/2007
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
Have seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year. Any ideas?
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn ---------------------------------------------- 1 | Change from 20->25 | 03/07/2007 09:00:00 ---------------------------------------------- 2 | Change from 25->35 | 10/09/2007 17:00:00 ---------------------------------------------- 3 | Change from 35->20 | 01/10/2007 16:00:00 ---------------------------------------------- 4 | Change from 20->26 | 11/10/2007 10:00:00 ----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I have a order table which has a orderdate and despatchdate i want to write a query in such a way that i want to get all the details from the table for a range specified date as the oderdate and despatchdate are user interactable. I search the google to solve this problem but could not find a answer
What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @Begin Date AND @EndDate. I want to include all of the @EndDate.
I am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.
So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.
Example:
Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now
If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.
Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.
I am very new to SQL and have a question, hopefully someone can answer.
I have a table of data, one of the fields is a date.
What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".
Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506 dates between 01/06/06 and 31/05/07 is fiscal year 0607
Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???
