Help With Date Parameter And If/else Statement
Jul 20, 2005
Hi,
I'm trying to run a select statement that takes includes an if/else
clause. I need to select the 'tran_date' between....
if the current month is greater than 10 i.e. after OCT then the
tran_date will be between '01-Oct' - plus current year or the
tran_date is '01-Oct' plus previous year.
and the current date
Here is my script so far:
SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,
b.description PROJECT_CODE, sysdate
FROM actrans a, acactivity b, dual c
WHERE a.resource_type = 'E'
AND a.acct_category = 'TIME'
and a.activity like '9-WW-357852%'
and b.activity = a.activity
and tran_date between
if to_char(sysdate, 'MM') > 10)
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY')
end
else
begin
a.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))
End
ORDER BY tran_date
Can anyone help me out with this? I need to run it as a job, but not
sure whether I should be using a stored procedure etc..
Thanks in advance.
George
View 1 Replies
ADVERTISEMENT
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
View 7 Replies
View Related
Jul 24, 2007
I am having report parameter end date I am showing the default value "5/21/2007 11:59:59 PM" in the end date paramter. And also I want to show date time format "(MM/DD/YYYY HH:MMS)" in the right hand side of the parameter. How to do this?
Thank You
View 1 Replies
View Related
Aug 11, 2013
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?
View 18 Replies
View Related
Nov 21, 2007
Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
Regards
Nataraj.C
View 2 Replies
View Related
Jan 24, 2008
Hello,
I am using the calender parameter and I need to convert my data date format to the one that matched that is returned on selecting a date from this calender. Can you show me what this format is.
how can I convert my existing date format to this format. The existing date format is 2007-07-26 21:27:13.000
thank you
Kiran
View 5 Replies
View Related
Jul 23, 2015
I am working into a t-sql where i need to expand the date within a date parameter. For example:
Start Date = '1/1/2014'
End Date = '12/31/2014'
I want to expand this as:
01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to
12/2014
View 4 Replies
View Related
Sep 15, 2006
Hi i'm trying to do this: SELECT Name, Address1 FROM Stores WHERE Name LIKE %@SearchText%"but it's not working. I assume my syntax is off?Thanks!
View 3 Replies
View Related
Apr 23, 2006
Hi there,
I am trying to use and SQL statement with a LIKE query with % at the begining and end of the parameter to get any combination of the search parameter i.e. last name.
My piece of SQL looks like this:
WHERE userUsername=@Username AND contactName LIKE @SearchTerm
I need something like this:
WHERE userUsername=@Username AND contactName LIKE %@SearchTerm%
However this does not work.
Any suggestions would be greatly appreciaed.
Regards,
TCM_
View 2 Replies
View Related
Jan 22, 2008
Hi,
I have a query that uses a parameter as criteria
e.g. type = :_type (i set p_type = 'Trad')
I want to change this parameter to be a range of values.
However when I change the query to be
type in (:_type) I get nothing returned if I set p_type to be 'Trad,New' but I do get something if it's just Trad.
Can anybody advise on the syntax? I've tried a number of variations.
View 1 Replies
View Related
May 27, 2008
Is it possible for an AND statement to take mulitple parameters? See below code:
SELECT tblQuestion.Question, tblAnswer.Answer
FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID)
WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14)
Is something like this possible?:
AND (tblAnswer.QuestionID = 14, 26)
Thanks
View 2 Replies
View Related
Oct 4, 2007
I am trying to pass a parameter to an sql statement that executes a stored proc. Here is my sql statement from the Execute SQL Task component.
USE [master]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_sendSMTPmail]
@To = N'john.r.carter@bankofamerica.com',
@Subject = ?,
@Body = NULL,
@Importance = NULL,
@Cc = NULL,
@Bcc = NULL,
@Attachments = NULL,
@HTMLFormat = NULL,
@From = N'bpm@bankofamerica.com'
SELECT 'Return Value' = @return_value
GO
I have the ByPassPrepare set to True.
Can anyone tell me why I can't get this to work.
Thanks,
JC
View 1 Replies
View Related
Dec 19, 2007
Hi,
I try to pass a parameter to a IN statement in SSRS but couldn't get it work. It likes following sample:
select * from T1 where name IN (@nameList)
I passed @nameList like 'aa', 'bb', ''aa'', ''bb''(two single quotations), all didn't work. I couldn't even get it work on "Reporting -> Data" screen.
What is the right way to pass a parameter to IN statement in SSRS? I am using C# and SSRS2005.
Thanks!
View 1 Replies
View Related
May 8, 2008
I have a select statement that I am using and wanted to know if there is a way to remove a parameter on the fly. What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do. Here is my code.
View 2 Replies
View Related
Jan 10, 2000
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'
Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
but I can't seem to come up with the right statement.
Any help greatly appreciated.
Thanks,
Judith
View 1 Replies
View Related
Mar 7, 2008
If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?
View 1 Replies
View Related
Jul 23, 2005
Hi all,I was just wondering if it could be possible to excecute a statementwhich is extracted from variabele, Example:declare @SqlStatement varchar (100)set @SqlStatement = 'select * from company'the @SqlStatement contains the actual sql query, is it possible somehow to straight away convert it into excecution?I've tried:exec @SqlStatementbut exec or execute are meant to be for proceduresAny idea?
View 1 Replies
View Related
Oct 19, 2007
Hi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View 5 Replies
View Related
Sep 5, 2007
Hi!
Is it possible to call proc with parameter in openrowset?:
declare @str varchar(100);
select @str = 'EXEC SP_HELPTEXT ''createReport''';
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str)
AS a;
Incorrect syntax near '@str'.
------------------
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC
SP_HELPTEXT ''createReport''') AS a;
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE
DB provider "SQLNCLI" for linked server "(null)" indicates that either
the object has no columns or the current user does not have
permissions on that object.
Best regards
B. D. Jensen
View 6 Replies
View Related
Jan 16, 2008
store procedure
@End_Date datetime
SELECT * FROM table1 WHERE PROCESS_DATE <= @End_Date
@End_Date format: 1/1/2008
PROCESS_DATE format: 1/1/2008 12:45:01 PM
I can not get the date after running the procedure using the value/format above. I think the problem is that SQL convert @End_Date value to 1/1/2008 00:00:00:AM, which makes 1/1/2008 12:45:01 PM > 1/1/2008
Since I only need to compare the date (no need time), how can I resolve the problem from the C# code or SQL level? Is that possbile to make 1/1/2008 default to the max datetime like 1/1/2008 11:59:59:PM?
View 4 Replies
View Related
Apr 2, 2008
Hi,
My boss would like to see on our report a code that would pull the date 01-01-2008 to the present. They want to be able to run this report whenever they wanted to. What is the best way to create this code.
Could I just use my field, which is "clm_dout" to say Clm_dout >= or is there another type of code I should use?
Thanks
View 8 Replies
View Related
Feb 21, 2006
Hello ........
I have a DateTime parameter in my report.
I want the last date of the current month selected as default in the date parameter(datetime picker ).
Can anybody help me ?
Thanks
View 7 Replies
View Related
Mar 22, 2007
hi and thanx in advance
in a report, i am getting data from a stored proc which take to date parameter fromdate and todate which are passed from report.
user select dates in datepicker control
but problem is that it not returning any record
Can problem be in date format difference in report and sql server ?
View 1 Replies
View Related
May 21, 2007
One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.
How is it possible to use the calender parameter without the report giving the error pls?
Thanks
View 4 Replies
View Related
Dec 19, 2014
Is there a way to set a default value for a sp parameter using a select statement(see code bellow)
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event
View 9 Replies
View Related
Jun 3, 2008
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END
View 3 Replies
View Related
Jul 20, 2005
In a 'Top n' type statement I wish to be able to insert the n valuefrom a parameter, within a stored precedure egHaving declared @pageSize as a parameter I want to run the followingtype of query :SELECT DISTINCT TOP @pageSize routeID, routeName FROMtblRoute_HeaderWhen I attempt to do so I get an error mesage indicating incorrectsyntax. I do not get an error message if I specify 'n' directly as inTOP 10Am I missing something or is this not possible within a storedprocedure?Best wishes, John Morgan
View 5 Replies
View Related
Nov 3, 2015
I am trying to calculate a measure based on parameter passed I have a liquidation rate of amount/facevalue when the dimension attribute is 'all' and i will like to the 'amount' to change based on the parameter passed and the 'facevalue' should not changed , i tried with the code below.
What I noticed is when i add a calculated member to return the currentmember of the cell member
measures.strat
as
[Strategy].[ICS].CURRENTMEMBER.uniquename
it comes back as
[Strategy].[ICS].[All]
even when I changed the default value for the parameter to another member like red
WITH
MEMBER
MEASURES.TEST
AS
((
CASE
WHEN [Strategy].[ICS].CURRENTMEMBER.uniquename = '[Strategy].[ICS].[All]'
THEN
[Measures].[Measures].[AMOUNT]
[code]....
View 4 Replies
View Related
Oct 20, 2006
Hello guys I hope everyone is doing well I have a question. I have a database thats an ADP. on one of the forms I have a command button that has a stored procedure in it that will allow them to search by TM# or name or whatever, (I have the code below) I would like them to be able to do the samething but by date rather then TM# or Name, how would I go about doing that with a datetime datatype??
thank you
ALTER PROCEDURE dbo.Search_ActiveConditionals
(@Enter_TM# int)
AS SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, INVESTIGATOR_COND, REASONFORCOND
FROM dbo.ACTIVE_CONDITIONALS
WHERE (TM# = @Enter_TM#)
View 2 Replies
View Related
Oct 5, 2007
Hi,
I have a situation where i need to have two parameters (startDate, endDate) and use them as filters for my query. But i don't want to have a stored procedure with these parameters. So how can i have the two date parameters as calendars to choose dates from?
View 2 Replies
View Related
Aug 7, 2007
Hello Im having a date problem
I have a parameter called @Param_Daterange with the following values
Last 30 days =Today.AddDays(-30)
Last 60 days =Today.AddDays(-60)
My SQL has a chart which uses the following SQL
SELECT Sum(dbo.tbl_OMDQ_Aggregates.Value) AS SumOfValue, dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun
FROM dbo.tbl_OMDQ_Aggregates
WHERE (((dbo.tbl_OMDQ_Aggregates.SaleMonth) IN (@Param_SaleMonth))and (status_desc IN (@Param_ChurnType))) and dbo.tbl_OMDQ_Aggregates.DateRun > @Param_DateRange
GROUP BY dbo.tbl_OMDQ_Aggregates.status_desc, dbo.tbl_OMDQ_Aggregates.DateRun;
In visual studio the chart is working fine, however in IE the data getting returned is only a small subset off the entire data, ...my IE language setting is at English Aus, I also created a textbox so i can see what value the daterange parameter is passing and it seems to be in the correct format...the graph is also in the right day/time format of dd/MM/yyyy. However i have no idea which date is getting passed incorrectly, somewhere the date is getting switched to American format however what is outputting on the screen is in the right format...just not the correct records are returning
can anyone please help
View 1 Replies
View Related
Feb 13, 2008
Hi all,
I have a report that have 2 date parameters (start and end date). How would I check that end date parameter move be equal or greater than Start date?
thanks
View 4 Replies
View Related