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

How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date

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

How To Use Default Parameter Values With A Date Parameter From A Cube/Reducing Parameters

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

SSRS Date Parameter - Showing Date Format On Right Hand Side

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

Date Parameter In SSRS - Allow Drop Down For A Date Range To Be Selected

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

Unable To Set Current Date In Date Picker Parameter

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

Date Time Format For Date Parameter

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

Transact SQL :: Expand Date Within Date Parameter

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

LIKE Statement With A Parameter Value

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

SQL Parameter And LIKE % % Statement

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

In Statement With Parameter

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

Multi Parameter AND Statement

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

Passing Parameter To Sql Statement

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

Passing Parameter To IN Statement

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

Remove Parameter In A Select Statement

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

Parameter In Declare Cursor Statement

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

Output Parameter Vs Select Statement

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

Parsing A SQL Statement As A Parameter And Executing It

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

Pass Column Name Using Parameter In SQL Statement...

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

Openrowset With Parameter In Exec Statement?

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

Date Parameter

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

Date Parameter

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

Date Parameter

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

Date Parameter

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

Date Parameter

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

SQL 2012 :: Set Default Value For SP Parameter Using Select Statement

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

Case Statement On Where Clause If Parameter =NULL

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

Can I Create A 'Top N' Statement Within A Stored Procedure Using A Parameter?

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

Analysis :: MDX SSRS Parameter In Case Statement?

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

Search Parameter By Date

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

Date Range Parameter

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

Date Parameter Problems

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

Parameter Date Validation...

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







Copyrights 2005-15 www.BigResource.com, All rights reserved