I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.
I am running a query but it never ends to run.
This is the function:
USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
This is how I am trying to use it in the View:
Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE
I'm trying to return a query based on the dateadd function. I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1. The Var1 variable I need to populate from the database too from a column called life_span which is an int data type. The error I get is An expression of non-boolean type specified in context where a condition is expected near select
My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table
I have events which require certain things be done several days before the event and things be done several days after the event. I attempted to use the DATEADD function to subtract 3 days from the event date. The SQL Statement I created did just that, but, it displays 3 days back from today's date.
There are 2 tables:
CalendarCategories -- Table
CalCategoryID -- int field CalCategoryName -- varchar field
CalendarEvents -- Table
CalCategoryID -- int Field Title -- varchar Field StartDate -- DateTime Field
I have to perform some tasks 3 days before the event. So, TODAY, I want to see a listing of those events which are scheduled for 3 days FROM NOW.
This is my current SQL Statement: SELECT DATEADD(d,-3,StartDate) AS [Update Payoffs & Ins], Title AS [Closing Description] FROM CalendarEvents WHERE datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate())
This SQL Statement takes TODAY'S events and gives them a date of February 20. See example of the results at http://www.joelwilliamslaw.com/DesktopDefault.aspx?tabid=141
This is what I have already done relative to my calendar listings:
Specific Event Types for the Current Month :
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Specific Event Types for the Current Day:
SELECT StartDate AS [Date and Time], CalCategoryName AS [Cls Type], Title AS [Closing Description] FROM CalendarEvents inner join CalendarCategories on CalendarEvents.CalCategoryID = CalendarCategories.CalCategoryID where (CalendarEvents.CalCategoryID = 1 OR CalendarEvents.CalCategoryID = 2 OR CalendarEvents.CalCategoryID = 3 OR CalendarEvents.CalCategoryID = 4 OR CalendarEvents.CalCategoryID = 20) AND (datepart(dd,StartDate)=datepart(dd,getdate()) AND datepart(mm,StartDate)=datepart(mm,getdate()) AND datepart(yy,StartDate)=datepart(yy,getdate())) AND CalendarEvents.ModuleID = 360 ORDER BY StartDate ASC
Hello group!I am having a problem with simplying my query...I would like to get customers' balance info based on how many monthssince they opened their accounts. The tricky part here is accountsstarting with '28' are treated differently than other accounts, theyare given 3 months grace period. In other words, for all otheraccounts, their month0 balance is the balance of their open_month, andmonth1 balance is the balance after the account is opened 1 month, andso on. But accounts starting with '28' month0 balance would be thebalance after the account is opened 3 months, and month1 balance wouldbe the balance after the account is opened 4 months, and so on.My query below works, but since some customers are more than 10 yearsold (more than 120 months), my query is endless! Does anyone know abetter way to do the same job? Many thanks!create table a(person_id int,account int,open_date datetime)insert into a values(1,200001,'11/15/2004')insert into a values(2,280001,'8/20/2004')create table b(account int,balance_date datetime,balance money)insert into b values(200001,'11/30/2004',700)insert into b values(200001,'12/31/2004',800)insert into b values(200001,'1/31/2005',900)insert into b values(200001,'2/28/2005',1000)insert into b values(280001,'8/30/2004',7000)insert into b values(280001,'9/30/2004',8000)insert into b values(280001,'10/31/2004',9000)insert into b values(280001,'11/30/2004',10000)insert into b values(280001,'12/31/2004',15000)insert into b values(280001,'1/31/2005',20000)insert into b values(280001,'2/28/2005',30000)--Ideal output--person_idacc_nomonth0_balancemonth1_balancemonth2_balancemonth3_balance1200000170080090010002280000110000150002000030000select a.person_id,a.account,month0_balance=casewhen a.account like '2%' and a.account not like '28%'thensum(case datediff(mm, a.open_date, balance_date) when 0then b.balance else 0 end)else sum(case datediff(mm, a.open_date, balance_date)when 3 then b.balance else 0 end)end,month1_balance =casewhen a.account like '2%' and a.account not like '28%'thensum(case datediff(mm, a.open_date, balance_date) when 1then b.balance else 0 end)else sum(case datediff(mm, a.open_date, balance_date)when 4 then b.balance else 0 end)endfrom a as ajoin b as bon a.account=b.accountgroup by a.person_id, a.account
Hi ALL!anyone can help me resolve this problem.I create SQL sentence and using DATEADD to Update DATETIME FIELDand i need Increase 1 Year and Month is 04 and day is 01.example: origin datetime field : 2007/06/26result after update is : 2008/04/01so by DATEADD(yy,1, datefield)?Have any expression for datefield to set month and day like what i need?Thanks .
:eek: Hi, I'm getting problem while using DATEADD Function.
When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.
hi, can something tell me what function do i need if i want to know what happen the last three hours? for example: dateadd (hh, -3, getdate()) it seems not working?!!
I need help in T-SQL.I am using DATEADD function and I want to add 6 months to a date. Butit does not return me the rusults, which I wante.g. SELECT DATEADD(m,'20040630') returns 20041230which is logical correct? But I want it to return end of month (i.e.20041231)Any help in this context will be highly appreciatedAbdul N. Khan
Problem: I have two nearly identical querys that are running. One of the query's is using the SQL DATEADD function. These query's are running on a SLQ2005 MSDE. There is now service pack four applied to this server. The question is: Are there any known issues with the DATEADD function in SQL 2005? This query is used in a report that has been in use for about two years. There were some changes made to the server (BIOS upgrade) including adding this service pack. These changes did not cause any other SQL issues. This query now takes almost five minutes to run. The nearly identical query (the difference is the date is passed in as a parameter rather than using the DATEADD function) takes from one to two seconds. These reports have a history of nearly identical running time.
What is a good way to try and correct the issue or at least to try and isolate the problem? Have there been any known issues with this function?
I am using the dateadd function to return a recordset. The function seems to allow me to plug variables into parameter2 if its a valid number type, and paramter3 as long as its a valid date type. How ever the function is not allowing me to use a variable into parameter1. I have tried a varchar and all other data types and can't get it to work. Any thoughts?
Hi, I encountered a strange error today. I have an Integer parameter "hours" that I am trying to use in my SQL Query.
DATEADD(hh, @hours, @startTime)
It works if I have it set such as DATEADD(hh, 8, @startTime), but I need that parameter there for what I need.
I get this error:
Error Source: System.Data Error Message: Failed to convert parameter value from Decimal to DateTime. I tried a variety of CInt and other conversion functions to no avail.
Im using the DateAdd Function to establish a future date base on the required time for a series of events to transpire. I'd like to exclude weekends, does anyone know a way to do this?
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp)Â Â Â WHEN 'Monday' then 'Monday'Â Â ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')Â END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
Heres My SQL to get me 2 hours back of Midnight, two months ago (so 11pm of the prev day)
SELECT DATEADD("hh",-2,DATEADD("m",-2,DATEADD(dd,-(DAY(GetDate())-1),DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)))) So I converted it into RS for my report start date, but it's not running...cant even get preview to load, it detects an error right away.,,just doesnt tell me what it is
I'm pretty sure I have the "h", "m" and "d" syntax right, I tried them all individually and they give me the correct dates...other than changing GetDate() to Now() I dont know what makes this diffenent...is it just too much nesting to Reporting Services?
I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
Hello...I am running into a problem while running a query..can some1 help..this is the query :**************SELECT *from Table Swhere S.dtDate1 BETWEEN dateadd(year,1,dateadd(month,-1,getdate()))AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-1,getdate())))))***************(first part of the date calculation comes out to be '2005-05-01' andsecond part is '2005-05-31')Here is the interesting twist:The query runs right if the first date in the between clause isentered directly i.e. the query wud run right if i rite it asSELECT *from vCustomerProductDetail Swhere S.dtDate1 BETWEEN '2005-05-01'AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-1,getdate())))))The funny part is the ending date in the between part is depending onthe starting date if u notice..so if there is somethg rong in thefirst part there shud be somethg rong with the second too if u get wati am saying but the query runs right with the second part left as itis..can some1 please help..thanks-Raghu
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work? All help is greatly appreciated!
I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function
SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length
I have several fuctions that I would like to share between my different script tasks in my SSIS package. I assume I do this by creating a custom class, but I cant quite figure it out. Can someone please point me in the right direction?
hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
*** edited by: master4eva *** Please enclose your code in </ code> tags (without the space between the "</" and "code"). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.
I have already done the editing to include the <code></ code> tags. *********
I have a trigger that fires this stored procedure during an update event. But i think i am getting an endless loop. Any idea? Thanks in advance.... <code> ALTER PROCEDURE TrigRetReqRecIDP1 @REID int
AS
Declare @RRID int Declare @intREID varchar(20) Declare @intIMID varchar(20) Declare @RetValint Declare cr cursor for select RRID from RequestRecords where REID=@REID and RRStatus = 'PE' open cr
fetch next from cr into @RRID
while @@fetch_status = 0 Begin select @intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk') insert into ImplementationTasks ( IMID, ITStatus, ITStatusDate ) VALUES ( @intIMID, '2', GetDate() ) SET @RetVal = @@IDENTITY Update RequestRecords set ITID = @RETVal, RRStatus = 'IA' where REID = @REID and RRID = @RRID
I have an SQL script that runs as part of a batch process in order to number line items in timecards. It basically consists of 2 nested cursors with a number increment and update inside the inner cursor. The problem is that it gets stuck (seemingly at random) in a loop (incrementing and updating within the inner cursor) until I get an arithmetic overflow and it just moves on to the next card. I'm new to my job as a dba and the boss is breathing down my neck.....any assistance would be GREATLY appreciated!!
declare LINES_OUT_OF_SEQ_CSR cursor for select TIMECARD_SEQ_NBR from EMPL_TMCD_VER_DETL where NEW = '1' order by TIMECARD_SEQ_NBR
open LINES_OUT_OF_SEQ_CSR
fetch next from LINES_OUT_OF_SEQ_CSR into @SEQ
while @@fetch_status = 0 begin declare LINES_TO_CHG_SEQ_CSR cursor for select TIMECARD_SEQ_NBR, LINE_ITEM_SEQ_NBR from EMPL_TMCD_VER_DETL where TIMECARD_SEQ_NBR = @SEQ order by LINE_ITEM_SEQ_NBR
select @NUM = 0
open LINES_TO_CHG_SEQ_CSR
fetch next from LINES_TO_CHG_SEQ_CSR into @TSQ, @TLI
while @@fetch_status = 0 begin select @SUM = @NUM +1
select @NUM = @SUM
update EMPL_TMCD_VER_DETL set TIMECARD_VERSION_ITEM_NBR = @NUM where LINE_ITEM_SEQ_NBR = @TLI and TIMECARD_SEQ_NBR = @TSQ
fetch next from LINES_TO_CHG_SEQ_CSR into @TSQ, @TLI end
deallocate LINES_TO_CHG_SEQ_CSR
fetch next from LINES_OUT_OF_SEQ_CSR into @SEQ end
i have created a job that i have scheduled to run every 10 min everything is configured well since i have tested preety everything their is to be tested and found that it was my last step wich as a fetch in it so i imagine that this fetch is making it loop over and over again. the job goes trought all the steps and starts back at the first step and keep going like that till i disable it here is my fetch statement and if you have any clue any help would be widely apreciated.
PS: i suspected it to be that fetch statement causing the havoc ;)
DECLARE TransactionNb_cursor CURSOR FOR SELECT TransactionNb, EqId FROM DetCom WHERE UpdCode = 'C'
OPEN TransactionNb_cursor
FETCH NEXT FROM TransactionNb_cursor INTO @TransactionNb, @EqId
WHILE @@FETCH_STATUS <> -1 BEGIN -- Vérifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom IF (SELECT UpdCode FROM EntCom WHERE TransactionNb = @TransactionNb and EqId = @EqId) = 'C' BEGIN CONTINUE END ELSE BEGIN RAISERROR (50006, 10, 0, @TransactionNb, @EqId) END
FETCH NEXT FROM TransactionNb_cursor INTO @TransactionNb, @EqId END
CLOSE TransactionNb_cursor DEALLOCATE TransactionNb_cursor
I have a table with two columns: OID and Cumulative (witch is the same type as OID) Each OID can have one or more Cumulatives.
Example of data: OID Cumulative 167 292 167 294 167 296 168 292 169 302 169 304
The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical). Example: 167->292->590 So the table would have on more row: OID Cumulative 295 505
I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure: OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.
How can I do the query? Is there a better way as my table with n columns?
I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?
I'm having a problem where I'm using a Execute SQL Task to retrieve a dataset and storing that in an object variable. Then on success of that execute sql task I use a foreach loop task to go through the dataset and do 2 tasks inside the foreach loop. When I execute this package I have ~12 records in the dataset however when I get to the foreach loop in the 2nd iteration it keeps repeating it. It acts like it is stuck on the second record (tuple) and never goes on. I'm using an ForEach ADO Enumerator in the foreach. I've even set a breakpoint on each iteration and no task fails in side the foreach loop. I'm completely perplexed why it will iterate to the 2nd record but get stuck there in an endless loop. I've tried this on 2 different computers (with different data values) and the same thing happens. Does anyone have any suggestions?
I have a package which runs fine, when I execute it with my account (e.g. double-click on the .dtsx file and run it).
Now I would like to establish a job, which starts the package. I created first a credential for my Account (which is a domain administrator account also for the box, where SQL Server is running on), then I defined a proxy to this credential.
In the job definition I changed the Run as... to this Proxy (it is a SSIS Proxy) and then I started the job.
Th job does NOT abend, it runs forever! So I have to stop it manually.
In the log I can see as last entry : "operation complete". It stops at a "Execute process task" where I call the bcp utility.
Does anyone has an idea, why a package can run forever?!?!
