I am having trouble finding a way to work with dates. Below is a very simplified sproc I am trying to get to work. It will later be expanded, but first things first:
CREATE PROCEDURE spCellarOp4
@ReportDate datetime = getdate
AS
SELECT FERMID, fldfermtank, fldfilltime, fldfermprod,
"Sample Test" = CASE
WHEN CONVERT(int, DATEDIFF(dd, fldfilltime, @ReportDate)) = 1 THEN ' it is 1 '
END
FROM vwUnfilteredFermenters
The error message is:
Server: Msg 241, Level 16, State 1, Procedure spCellarOp4, Line 0
Syntax error converting datetime from character string.
Does anyone know the system tables I need to query to produce a report of stored procedures (SQL 2005) that had any changes made to them in a user-specified date range?
In SQL 2005, I saw the canned database reports, but this one didn't exist. Any help would be greatly appreciated.
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
All I am starting to play with a datetime field in sql server 2005 i wanted to write the date and time to my database in seperate fields but if if format my input to dd-MMM-yy i end up with 02-february- 2008 00:00 and if i try for the time i get 01/01/1900 12.00 i can put them both in the same field, thats not a problem for be but how do i then do an sql query to get backj say all records where the date is between the 01-jan-2008 and 02-feb-2008 Maybe if some one can point me at a tutorial it may assist me thanks gibbo
I need to find Monday's DATE (and Tuesday's, and Wednesday's, etc.) of Week number 44 of the year 2005 for my stored procedure. Does anyone know how I can do that? Jon
I've got a table with some datetime fields in it.One field (call it field 1) is of the form mm/dd/yyyy and the other two(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundrethsof a second.I'm getting the difference between field 2 and 3 using (datediff(ms,access_time, release_time )/1000/60.). This seems to work fine.However, in some other cases I'd like to add field 1 to field 2 andthen manipulate the result. This is where it gets weird.If I do it like this: Convert(varchar 20),record_date+access_time,100),it adds field 2 ok but subtracts two days. So for example 3/1/05 +10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts twoadditional days for no apparent reason. If is userecord_date+2+access, then this returns the correct answer.If I try to use Convert(varchar(20),dateadd(ss,record_date,access_time),100) sql server complains Argument data type datetime isinvalid for argument 2 of the dateadd function.Basically I'd just like to know how to add and subtract fields 1(mm/dd/yyyy format) and 2 (hh:mm:ss:xx format).As a bonus question, is it possible to get an average time for severaldifferent times? For example the average time between 10:30 and 11:00would be 10:45.regards,-David
I'm trying to add records to a table that includes datetime value using INSERT query. No matter what date value I'm giving the query, the value saved is 1/1/1900 00:00 . Also, I'm interested in saving only the short date, without time. I thought changing the type from datetime to smalldatetime would do the job, but it didn't help.
I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows: iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll 1871893440 1 2006-04-24 note1 1871893545 1 2006-06-22 note2 1871893547 1 2006-06-22 note3 1871893653 1 2006-08-10 note4 1871893653 2 2006-08-10 note5
funtion will return = 2006_08-10 note4 note5 for iincidentid=187189 ----------------------------------------------------- CREATE FUNCTION dbo.getIncidentNotesRev(@iIncidentID int) RETURNS varchar(8000) AS BEGIN declare @incidentId int declare @worknoteid int declare @worknotesaveid int declare @seqnum int declare @dtEntryDate smalldatetime declare @worknoteall varchar(8000) declare@allnotes varchar(8000) declare @currentWEDate smalldatetime declare @beginWEDate smalldatetime
select @allnotes='' select @currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date select @beginWEDate = DATEADD(d, - 28, @currentWEDate)--get the last 4 weeks
declare CursorIncident CURSOR LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw where iIncidentId=@iIncidentID order by iWorkNoteId
OPEN CursorIncident FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
--store 1st record of cursor select @worknotesaveid =@worknoteid WHILE (@@FETCH_STATUS=0) BEGIN if @dtEntryDate >=@beginWEDate AND @dtEntryDate <= @currentWEDate Begin if @worknotesaveid <> @worknoteid Begin Select @allnotes = @allnotes + @dtEntryDate + @worknoteall End else BEgin select @allnotes = @allnotes + @worknoteall End
select @worknotesaveid = @worknoteid --save next worknoteId End else Begin select @allnotes='' End FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall END --WHILE (@@FETCH_STATUS=0)
CLOSE CursorIncident DEALLOCATE CursorIncident
return @allnotes END
---------- Function not working right. I appreciate any help. Thanks in advance.
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. */ AS BEGIN
--check if one of the dates is null if @StartDate is null or @EndDate is null RETURN 0
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control..... No rows affected. (0 row(s) returned) No rows affected. (0 row(s) returned) Running [dbo].[sp_UPD_MESample_ACT_Formdata] ( @ME_Rev_Nbr = 570858 , @A1 = No , @A2 = No , @A5 = NA , @A6 = NA , @A7 = NA , @SectionA_Comments = none , @B1 = No , @B2 = Yes , @B3 = NA , @B4 = NA , @B5 = Yes , @B6 = No , @B7 = Yes , @SectionB_Comments = none , @EI_1 = N/A , @EI_2 = N/A , @UI_1 = N/A , @UI_2 = N/A , @HH_1 = N/A , @HH_2 = N/A , @SHEL_1 = 363-030 , @SHEL_2 = N/A , @SUA_1 = N/A, @SUA_2 = N/A , @Cert_Period = 10/1/06 - 12/31/06 , @CR_Rev_Completed = Y ).
No rows affected. (0 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[sp_UPD_MESample_ACT_Formdata]. The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0). And yet every time I try to update the record in the formview online... I get Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see. Does anyone have any tips or tricks or info that might help me?
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks, Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry ( @EntryLogID int, @ProjectID int, @NewCategoryID int, @Expended decimal(10,2) ) AS DECLARE@OldCategoryID int
********************************************* BP_GetLedgerCategory ********************************************* ALTER PROCEDURE BP_GetLedgerCategory ( @EntryLogID int ) AS
SELECT CategoryID FROM BP_EntryLog WHERE EntryLogID = @EntryLogID
RETURN
********************************************* BP_UpdateCategories ********************************************* ALTER PROCEDURE BP_UpdateCategories ( @ProjectID int, @NewCategoryID int, @Expended decimal(10,2), @OldCategoryID int ) AS
UPDATE BP_Categories SET CatExpended = CatExpended + @Expended WHERE ProjectID = @ProjectID AND CategoryID = @NewCategoryID
UPDATE BP_Categories SET CatExpended = CatExpended - @Expended WHERE ProjectID = @ProjectID AND CategoryID = @OldCategoryID
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6)) as DECLARE @CityID integer declare @StateID integer declare @ZipID integer set @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null set @CityID= EXEC GetCityID(@City); set @StateID= EXEC GetStateID(@State); insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID) if @@ERROR = 0 SET @ZIPID = @@Identity select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs in the above stored proc? I mean what is the syntax??
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID. What I have tried thus far: UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID) This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated. Thanks! Amanda
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something? SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle) ArticleID - int (PK) ArticleTitle - varchar ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect. but when using a sproc, i couldn't get anything The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text) AS SELECT ArticleID, ArticleTitle, ArticleContent FROM dbo.JournalArticle WHERE (ArticleAbstract LIKE @srch) GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything. I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC: ___________________ CREATE PROCEDURE dbo.sp_addMembershipRole @INCID Int AS declare @literal NVarChar (10) SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role]) VALUES (@INCID, @literal) GO ___________________
CODE: ___________________ using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Security.Cryptography; using System.Web.Security;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click); this.Load += new System.EventHandler(this.Page_Load);
} #endregion
public void btnAdd_Click(object sender, System.EventArgs e) { int intContactID; string StrCID = Session["CID"].ToString(); intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text) { lblError.Text = "Retyping of your desired password did not match. Please try again."; return; }
create procedure GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint) as begin declare @ZipID integer declare @AddrID integer set @AddrID=1 if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2) return(@AddrID) end GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6)) AS BEGIN DECLARE @CityID integer DECLARE @StateID integer DECLARE @ZipID integer
SET @ZipID=2 set @Zip5=lTrim(@Zip5) if @Zip5<>'' SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5) if @ZipID is null
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
Hi, I am trying to Implement Multi parameter... If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'. This is my sproc ALTER Procedure [dbo].[usp_GetOrdersByOrderDate] @ClientId nvarchar(max)= NULL, @StartDate datetime, @EndDate datetime AS Declare @SQLTEXT nvarchar(max) If @ClientId IS NULL Begin Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC END ELSE BEGIN SELECT @SQLTEXT = 'Select o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId FROM [Order] o Inner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserId WHERE cp.ClientId in (' + @ClientId + ') AND o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + ' ORDER BY o.OrderId DESC' execute (@SQLTEXT)
