Can't Use Parameter With DateAdd Function??
May 18, 2007
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.
Any ideas?
View 5 Replies
ADVERTISEMENT
Feb 21, 2008
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 .
View 2 Replies
View Related
Jul 23, 2007
Hi,
Can someone please tell me why the following won't work:
Code:
SELECT * FROM Validation where CourseDate > DateAdd(wk, -3, GetDate())
I want to select all records where the coursedate is more than 3 weeks old.
Thanks.
View 2 Replies
View Related
Jan 3, 2005
: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.
View 10 Replies
View Related
Nov 9, 2006
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?!!
View 2 Replies
View Related
May 23, 2008
I'm using a query that is not retrieving all of the data it should because of the datetime stamp. If I use the following in the where clause:
{table.date} between '4/1/2008' and dateadd(day, 1 '4/30/2008')
that works fine for April but not for other months, where I might get more data from the following month than I should.
Is there a way to add hours, minutes, and seconds (23:59:59), all at the same time, without moving ahead an entire day?
View 8 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Sep 7, 2007
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?
Thanks for any help!
View 2 Replies
View Related
Aug 22, 2000
DATEADD(par1, par2, par3)
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?
View 1 Replies
View Related
May 11, 2007
Hey guys I have the following in my SQL statement:
DATEADD(hh, @hours, @startTime)
hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:
DATEADD(hh, 8, @startTime)
The first version is what I need so that the user can control from a parameter and I get the following error:
Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.
Thanks!
BJ
View 2 Replies
View Related
Mar 7, 2008
I currently have this statement:
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
I would like to return however instead of column name 'MonthLessEleven' the actual month and year that is -11 from getdate().
I've tried the month(dateadd("m',-11, getdate())
but it returns syntax errors. I'm doing this in Visual Studio. Could anyone help me out? Thanks.
View 10 Replies
View Related
Jan 4, 2008
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?
Thanks in advance
Alex
View 1 Replies
View Related
Apr 11, 2015
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
View 2 Replies
View Related
Mar 24, 2008
I am using the following code in my SQL stmt in my OLE BD Source stmt:
WHERE ICINVENTORY.ICINVLastChgAt > ? AND ICINVENTORY.ICINVLastChgAt <= DATEADD(mi,?,?)
My parameters are as follows:
0 - User:LastSalesLoadDate DateTime variable
1 - User:Load Interval Int16 (or Int32)
2 - User:LastSalesLoadDate
When I try to close the program I get the following error:
"Argument data type datetime is invalid for argument 2 of dateadd function. If I can't use a datetime data type for the date time part of the dateadd, what can I use?
The exact same code runs without error in an EXECUTE SQL task.
Thanks.
View 4 Replies
View Related
Aug 3, 2015
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
View 5 Replies
View Related
Feb 23, 2004
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
Your assistaince is much appreciated.
Joel
View 6 Replies
View Related
Jun 15, 2015
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?
View 7 Replies
View Related
Jan 13, 2014
I want to set the default parameters for a function. I;d like to set the date start date to current date and end date for the last 90 days. how to make this work?
Create Function HR.Equipment
(
@startdate Date =(Convert(Date,DATEADD(DAY,-1,GETDATE())),
@enddate Date = (Convert(Date,@StartDate-90)
)
RETURNS TABLE AS RETURN
(
SELECT
EquipID,
EmpName,
IssueDate
FROM HR.Equipment
WHERE IssueDate <=@StartDate and IssueDate >=@EndDate
)
GO
View 5 Replies
View Related
Jul 20, 2005
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View 3 Replies
View Related
Feb 1, 2006
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need.
Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error. Ugh. Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)
View 1 Replies
View Related
Jan 20, 2006
Hi,
this has started from me wanting to write a report that allowed the user to choose from a drop down list of how many months they want the report to cover. IE i want the report for the last 3 months, or 5 months or ...
To do this i created a report with a value list parameter (values are negative integers to give the necessary start date for the reported rows) and had that parameter in the where clause, along the lines of ...
select *blah*
from *blah*
where ( *myfield* > dateadd (mm, @MonthParam, GetDate())
i get a sql error (ie an error from the database not reporting services) saying that the variable hasnt been declared.
I have worked around the problem in this instance but is there a 'feature' in RS that means report parameters cannot take part in functions?
TIA
View 2 Replies
View Related
Apr 20, 2007
I am writing a function which will take two parameters. One the fieldto be returned from a table and second parameter is the ID of therecord to be returned.Problem is it's not returning the value of the field specified in theparameter but instead returns the parameter itself. Is there afunction that will get the parameter to be evaluted first?ALTER FUNCTION [dbo].[getScholarYearData](-- Add the parameters for the function here@FieldName varchar(50), @ScholarID int)RETURNS varchar(255)ASBEGIN-- Declare the return variable hereDECLARE @ResultVar varchar(255)-- Add the T-SQL statements to compute the return value hereSELECT @ResultVar=EXECUTE(@FieldName)FROM dbo.qmaxScholarYearID INNER JOINdbo.tblScholarYears ONdbo.qmaxScholarYearID.ScholarID = dbo.tblScholarYears.ScholarID ANDdbo.qmaxScholarYearID.MaxOfScholarYearID =dbo.tblScholarYears.ScholarYearID-- Return the result of the functionRETURN @ResultVarEND
View 10 Replies
View Related
Dec 18, 2013
Need to INSERT into a different table the function value results in SELECT from a table for PurchorderNum and QtyOrder and not sure how
ALTER proc [dbo].[spCreateContainerFill]
(@containerID as nvarchar(64),
@lotNum as bigint,
@customerID as int = 1164,
[code]....
View 1 Replies
View Related
Mar 2, 2008
I'm trying to write a function that I can run with passing the table name as a parameter. I want to return an integer. The tables wll be differnet types (different colums) but they all have a similar field that i want to get a count of. Someting alng the lines of this:
Create FUNCTION [dbo].[fn_GetItemsToPromote]
(
@TableName nvarchar(100)
)
RETURNS int
AS
BEGIN
Declare @SQL nvarchar(500)
return select count(*) from [@TableName]where promote = 1
END
It doesnt like the @TableName. Can anyone show me how to do this correctly?
Thanks!
View 4 Replies
View Related
Mar 10, 2008
I have to pass 3parameters in function,
@begindate,@enddate and @group_type..
but in @group_type should be - state,zipcode and country from salestable
inview :vwstzipcont
create view vwstzipcont
as
select distinct s2.stype,s3.itemnmbr,s2.docdate,s3.state,s3.zipcode,s3.country from Salestable s3
left outer join (select distinct stype,docdate from salesdisttable) s2
on s2.stype = s3.stype
where s2.soptype = 2
go
create function mystzipcont
( @begindate datetime, @enddate datetime, @group_type char(70))
RETURNS TABLE
AS
RETURN
(Select distinct t.docdate,t.itemnmbr,t.index,t.group_type from
(
select distinct
vs.docdate,vs.itemnmbr,
p.index From Pubs P
inner join vwstzipcont vs
on vs.index = p.index
Where (vs.docdate between @begindate and @enddate)
and @group_type ) as t
order by t.itemnmbr,t.docdate
end
how can i assign @group_type variable or t.group_type? in s3.state,s3.zipcode,s3.country
can anyone tell me? what condition should be in where clause for this variable?
thanks
View 14 Replies
View Related
Jul 20, 2005
I am trying to select a group of records based on a parameter valuepassed to the db from a web page. The value comes in as @Status andhas a list of statusID's: (1,2,5,9) I've tried to use"Where table.status IN (Select * from @Status AS ValueList)"And also tried"Where table.status IN (@Status)"And neither worked. Any suggestions?
View 2 Replies
View Related
Dec 12, 2007
I've created a function that converts the rows of a column into a delimited string using a passed cursor and delimiter character. In the past I did this in Oracle and called it as shown in the following example:
SELECT Table1.ID, Table1.FirstName, Table1.LastName, fnDelimitRows(CURSOR(SELECT Table2.CourseName FROM Table2 WHERE Table2.StudentID = Table1.ID), ',') AS AssignedCourses FROM Table1
23 John Smith CS101,MT200,BIO100
43 Julio Johnson CS200,ENG100,MT300
How would I pass a cursor into a function in SS like I did above in Oracle?
Thanks!
View 3 Replies
View Related
Apr 26, 2006
What SQL Function Criteria string replaces [forms]![myForm].[myField]?
I have a function that I want to pass criteria to from a drop down list. I tried using the same Access string in the Function but it does not work.
View 1 Replies
View Related
Mar 21, 2008
I have a report with the following dataset query:
Code Snippet
SELECT ...,
dbo.getActivitySinceBeginningYear(..., @countryId ,...) AS HoursYear
FROM ....
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
AND ...
GROUP BY ...
countryId is defined as a String multi-valued parameter.
The getActivitySinceBeginningYear is defined as follows:
Code Snippet
ALTER FUNCTION dbo.getActivitySinceBeginningYear
(
...
@countryId ntext,
...
)
RETURNS decimal(18,5)
AS
BEGIN
declare @return decimal(18,5)
set @return = 0.0
SELECT @return = SUM(detail.HOURS_WORKED)
FROM ...
inner join charlist_to_table(@countryId, Default) f on detail.COUNTRY_ID = f.str
WHERE ...
RETURN @return
END
This function works as expected if I transmit one ID or several.
The report works fine if I check only one ID.
If I check several IDs, the report displays this error:
Procedure or function dbo.getActivitySinceBeginningYear has too many arguments specified.
Why???
My second problem is with the Where clause:
Code Snippet
WHERE cast(detail.COUNTRY_ID as nvarchar(max)) IN (@countryId)
If I do:
Code SnippetWHERE detail.COUNTRY_ID IN (@countryId)
I have the following error:
Conversion failed when converting the nvarchar value '3,5' to data type int.
(I transmitted 3 and 5 for countryId)
That's why I tried to use a cast, but the data returned by the query is wrong when I transmit several value.
??
View 7 Replies
View Related
May 1, 2006
I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM d_Time_School_Year
WHERE (Year_Key = ?)
This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this
SELECT Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM fn_TimeDimension (?)
But SSIS doesn't like this. When I click on parameters command button I get and error like this
"Parameters cannot be extracted from the SQL command. The provider might not help.........
Syntax error, Permission Violation, or the non-specific error(Microsoft SQL native Client)"
Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?
Thanks
Jemini Joseph
View 10 Replies
View Related
Feb 26, 2008
hi all
i am using VS 2005 with SQL Server 2005 and i faced a problem that need to be solved urgently...
i want to make a function that take a table as input parameter which is the output of a stored procedure (Record set)...
first i found that to make w table be as input parameter you must create type of that table first but i found that sql server 2005 doesn't have the 'table' as a type...
please any help will be appreciated
thanks in advance
View 2 Replies
View Related
May 20, 2008
i have on table temp and it has column name fhID, now that column name and table name comes as parameter in stored procedure from c#.
now stored procedure has code somthing like below
@column nvarchar(50)
@tbname nvarchar(50)
-- both are parameter comes from c#
decalre @maxid
select @maxid=max(@column) from @tbname
rather than prints the maximum values of "fhid" it prints column name itself.
now i how can i achive actual working of max function using parameter.
is there any other way to pass parameter in aggregate function or it is not allowed to pass.???
plz help me,
thanks
vishal parekh
'fhid'
now how can
View 7 Replies
View Related
May 24, 2008
Hi all.
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
All ideas are welcome. Thanks.
View 1 Replies
View Related
