Using A Year Parameter In A Date Condition
Mar 11, 2008
I am collecting a year parameter from my user and want to use that year as part of a complete date condition.
So I collect the year say '2003' from my user.
A condition must be applied so that the date must be after 01-APR-...and here is where i want to utilize the parameter @Year. So I can apply the same date condition depending on what year we are looking at.
I am currently using the following logic:
(DATEPART(yy, CANCELLATION_DATE) = @Year)
AND
(DATEPART(mm, CANCELLATION_DATE) >= 04)
AND
(DATEPART(dd, CANCELLATION_DATE) >= 02)
This causes problems as the date can be only Jan, Feb, Mar, Apr 01 or 02. What I need is to gather all dates prior to Apr 02, with the year paramter to follow.
We originally developed this querty using Oracle so idealy there would exist a pl-sql to t-sql conversion tool out there! Anybody know of one?
Here is how the pl-sql is structured:
CANCELLATION_DATE >= TO_DATE('02-APR-'||:"Year",'DD-MON-YYYY') ) ) )
Thanks in advance,
mpaskevi
View 3 Replies
ADVERTISEMENT
Dec 14, 2006
I have one matrix that shows the CrashCount (measure) by month. Looks like this:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec
25 90 100 55 52 55 22 55 22 35 65
The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as
=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?
View 6 Replies
View Related
Aug 30, 2004
I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:
CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()
And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?
I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?
Thank you,
Cesar
View 4 Replies
View Related
Jul 20, 2005
Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel
View 2 Replies
View Related
Apr 19, 2007
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
View 4 Replies
View Related
Feb 4, 2008
Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck
View 2 Replies
View Related
Apr 14, 2008
Hello All,
i have three textboxes in a page and i want fill those textboxes with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne
View 3 Replies
View Related
May 15, 2015
I have a list of students who have taken a test on different dates.
I need a function to calculate year ago date from the date of test.
eg Sam took the test on feb 23 2015
I need to get a feb 23 2014 to see what subjects he studied from feb 23 2014 to feb 23 2015.
How can I do that.
View 3 Replies
View Related
Jun 10, 2008
My boss would like to see one report with parameter promts. I tried doing this, but I keep getting an error. Can someone help me out here?
Thanks.
WHERE
(@YEAR(CLM_Dout) = YEAR(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND MONTH(CLM_DOUT) = MONTH(GetDate()))or
(@YEAR(CLM_DOUT) = YEAR(GetDate()) AND DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))
View 9 Replies
View Related
Sep 12, 2007
I would like to default my first parameter, which is year, to the current year. I'm querying against a cube. I've tried setting the default =Non-queried =year(today), tried a few MDX field names, and also tried setting the current year as the default in my data set. I can't seem to get the parameter to accept a default.
View 3 Replies
View Related
Nov 13, 2007
I have a year-month hierarchy that I am using as a parameter in a report. I'm using the From query choice for available values and using the ParameterValue.
My parameter list displasy as:
2006
1
10
11
12
2
3
...
2007
1
10
11
12
2
3
4
Is there a way to get this to display months as integers rather than strings?
Thanks.
View 5 Replies
View Related
Jan 23, 2008
I want to write an Query for a report that has 7 parameters. I want to allow Null for these parameters but at the same time in the Query i want to put an AND condition for all the parameters. I cannot put an OR condition.
For Example;
If i have a report with 2 parameters then the script will have 2* 2 If conditions
something like:-
if( @parameter1 is not Null and @parameter is not Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1 and parameter2 = @prameter2
if( @parameter1 is not Null AND @parameter2 is Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1
If( @parameter1 is Null AND @parameter2 is Not Null)
select A, B, C
From Table 1
Where parameter2 = @parameter2
If( @parameter1 is Null AND @parameter2 is Null)
select A, B, C
From Table 1
For 7 parameter there will be way too many If conditions. Is there an better approch to write a Query for such a situation?
View 11 Replies
View Related
Aug 29, 2007
Hi,
I have a subreport added to the main report and I want to make this report visible only when the parameter value is met.
Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports.
Any help is greatly appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
May 21, 2008
hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView
@Year as varchar(10),
@Country as varchar(10),
@Family as varchar(10),
@Manu as varchar(10),
@Status as varchar(10),
@Type as varchar(10),
@Operator as varchar(10)
AS
SELECT * FROM ViewofAll
WHERE
ProductionYear = @Year and
CountryName = @Country and
Family = @Family and
Manufacturer = @Manu and
Status = @Status and
Type = @Type and
OperatorName = @Operator
RETURN and below how i call the procedure : Dim connection As SqlConnection
connection = New SqlConnection()
connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True"
connection.Open()
Dim command As SqlCommand
command = New SqlCommand("selectFromView", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters
command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Item("@Year").Value = myArray(0)
command.Parameters.Item("@Country").Value = myArray(1)
command.Parameters.Item("@Family").Value = myArray(2)
command.Parameters.Item("@Manu").Value = myArray(3)
command.Parameters.Item("@Status").Value = myArray(4)
command.Parameters.Item("@Type").Value = myArray(5)
command.Parameters.Item("@Operator").Value = myArray(5)
DSGrid.SelectCommand = command.CommandText With DSGrid
.DataBind()
End With i'm not really familiar with stored procedures ...any one can help me plz ? thanx in advance .
View 3 Replies
View Related
Jun 23, 2008
Hi ALL,
Well, i have one view:
create view vwdate
as
select custno, custname, city, date
from salestab
where date = ????
well, this question mark...i have to fill..
like i want if month is june then i want for whole may month results of custno, custname and city..
if month is july then i want results for whole june month..
means for previous month results i want whatever the date is for that particular month..
Example:
if date='06/15/08'
then results will be
custno custname city date
---------------------------------
1 AAAA NJ 05/01/08
2 SDS CT 05/04/08
........................ 05/18/08
.......................... 05/30/08
if date='06/30/08' OR '06/1/08' then also results will be same..
so i want for previous month results..
so can anyone help me to get the correct condition in where clause?
Thanks ALL..
View 2 Replies
View Related
Jan 11, 2007
How can i write a condition for like isdate(Fields!DueDate.value)
I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.
Thank you very much for the information.
*********************************************
=IIF(isdate(Fields!DueDate.Value), ................
********************************************
View 3 Replies
View Related
Aug 10, 2004
I have a table in sql server, i need to import this table to another
database in same sql server using DTS, In the table, we have a field called
'qualDate', I need to import the record that the qualDate is in the date of
today and back off four years, for example, today is 8/10/2004, back off four
year should be 8/10/2000, so i need only the record that qualDate is between
8/10/2000 to 8/10/2004. And this date should be changed daily. Tomorrow, it
should change to qualDate is between 8/11/2000 and 8/11/2004. How can i do this? it should be done every day! How to do in where clause. Thanks.
View 4 Replies
View Related
May 22, 2008
I have a Actual_dt column that is a type of date. I am trying to get all the values from the table that have the date less than 2008/01/01.
Please help.
Thanks
View 1 Replies
View Related
Jul 27, 2006
Does anyone know a script that will give the year to date? I'm doing a sales report and need to pull year to date information.
View 1 Replies
View Related
Mar 8, 2008
Hi,
I need to see if a record has been inserted into a table, and the condition is if the record has been inserted for the current year and month and day, if not, I can insert a new row.
SO I need to do:
IF NOT EXISTS (SELECT * FROM myTable WHERE Created = @Created)
But it has to be comparing the year,month and day (excluding the time part of the datetime type).
View 9 Replies
View Related
Feb 28, 2008
Hi !
I've got a list like this one:
uniqueID
technicianID
StatusID
Date
1
1133
2
01-01-2003
2
1133
2
01-03-2003
3
1133
2
01-01-2004
4
1133
3
01-08-2004
5
1133
3
01-02-2007
6
1133
4
01-04-2007
i need to select only the last rows of each year :
2
1133
2
01-03-2003
4
1133
3
01-08-2004
6
1133
4
01-04-2007
How can i achieve that , i have tried with max, but it does not retrieve all the fields.
thanks
View 7 Replies
View Related
Jul 21, 2015
I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .
View 5 Replies
View Related
Mar 31, 2008
Hi guys.
i have a field in a table which datatype is varchar and i m storing datetime and number in it.. but when i try to use select statement like ( between startdate and enddate) or number (= ) operator it's not working..
select * from table
where cast(Field as datetime) between @startdate and @enddate
second query that i m trying to exectue is
select * from table
where cast(Field as bigint) = @Fieldid
but both are not returning any value...can anyone tells me where i m doing mistake
View 21 Replies
View Related
Aug 12, 2007
First I have to apologize about posting the problem here , since it not actually relate to SQL Server
I am really new to database programming.
I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database
I can insert date in to table using statement like this
insert into tableXXX (date_field) values ('1/2/2007')
but when i tried to query it using this statement
select * from tableXXX where date_field = #1/2/2007#
Nothing return
I also tried to use #1-2-2007# , #01-02-2007# but it all the same
Can someone tell me how to successfuly use date as a query condition ?
Thank in Advance
View 2 Replies
View Related
May 14, 2004
Hi,
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....
Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
View 5 Replies
View Related
Jun 8, 2004
Hi,
I have 2 dimensions Time and Product....
If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:
Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):
Total Sales YTD Sales
----------- ----------
$ 50,000 $ 125,000
Again, I'll appreciate your help in writing the MDX for this.
View 3 Replies
View Related
Aug 5, 2004
Hi Guys,
Can anyone please let me know How to find the first day of the week, if I know the weekno and the year.
For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week
for the above query the date will be eg:26/06/2003
I really appreciate your Help
Thanks:confused: Find the Date if I know the week and year
View 3 Replies
View Related
Mar 19, 2007
i have this SQL table with a year, week and day-of-week field, but no field containing the actual date
is there a way (e.g. in a view of the SQL table) to transform the year/week/day combination into a date string (e.g. format dd/mm/yyyy) ?
View 7 Replies
View Related
Jan 12, 2005
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
View 6 Replies
View Related
May 14, 2004
Hi,
My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)
I'll appreciate your help.
YearMonthDerived Date
----------------------
2004101-Jan-2004
2004201-Feb-2004
2004301-Mar-2004
and so on ....
Many thanks in advance. I'll appreciate your help
P.S.
Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them
View 7 Replies
View Related
Feb 11, 2014
Can I get a date from a week number in a certain year, like I know it's week 7 in 2014 (I may know the month.)
View 7 Replies
View Related
Nov 9, 2006
Hi,
I have a table with field Msg_Time. In this field some records are there with date and time.
For eg:-
11/7/2006 2:35:00 AM,
11/1/2006 2:35:00 AM,
10/15/2006 2:35:00 AM,
11/7/2005 2:35:00 AM,
11/7/2005 2:35:00 AM,
8/26/2005 2:35:00 AM,
3/2/2005 2:35:00 AM etc
I wrote the query like this
"select datepart(year, Msg_Time) from C20_Messages"
This query result is:-
2006
2006
2006
2005
2005
2005
2005
Here I want to get only the year part without repeating.
I mean
2005
2006
How to do pls help me
with regards
shaji
View 3 Replies
View Related
Feb 16, 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related