Year Function In Derived Column Gives Error
Jan 2, 2008Hi ,
Year function in derived column gives error if the incoming date is less than 1/1/1753. Is this Issue or required behaviour
Thanks
Dharmbir
ADVERTISEMENT
Round Function In Derived Column Transformation
Oct 31, 2007Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
Round Function In Derived Column Transformation
Oct 31, 2007Can somebody please help me with the implementation of a logic in round off to the left of a decimal point.
Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help
Value x Excel SSIS
627900 16200 16221
187000 4800 4831
277760 7200 7175
763000 19700 19711
1387500 35800 35844
1465200 37900 37851
2725000 70400 70396
292800 7600 7564
317200 8200 8194
The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
How To Use Coalesce Function In Derived Column Component?
Jan 19, 2007Hi all,
You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example?
Thanks! Any reply or comment is highly appriciated.
Robert
Derived Column Returning No Data On GetDate() Function
Aug 1, 2006Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies View RelatedSample Problem : Function Replace() In Derived Column
Mar 22, 2007Hello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
Extending Derived Column Transform With Custom Function Library
Feb 16, 2007When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task
Jul 22, 2015how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
View 4 Replies View RelatedFunction That Works In Sql Server Management Studio Does Not Work In Derived Column Transformation Editor
May 12, 2007Hi
I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.
I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.
Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?
Thanks for the look see...
******************************
ALTER FUNCTION [dbo].[convdate]
(
@indate nvarchar(10)
)
RETURNS nvarchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @outdate nvarchar(10)
set @outdate =
substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+
substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+
substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2)
RETURN @outdate
END
********************************
And the error...
expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.
Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.
Apr 3, 2007
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Derived Column Error
Apr 15, 2008Hi,
I have just started learning SSIS. I have created a new package which is as follows :
SOURCE QUERY ----> Derived Column ------> Destination Customer Info
Source Fields are
FirstName
LastName
OrgCode
Phone
Ref1
The Derived column is adding a new Column name FullName and the friendly expression for it is FirstName + "Null" + LastName
Destination has all the columns are same as source columns with similar datatypes.
When i start the debugging process i get the following error:
[Destination - CustomerInfo [29]] Error: Column "FullName" cannot be found at the datasource.
[Destination - CustomerInfo [29]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "Destination - CustomerInfo" (29) failed the pre-execute phase and returned error code 0xC0202025.
Please let me know where i am going wrong.
Thanks
Transact SQL :: Function To Find Last Date Of Month One Year Ago - RETURNS ERROR
Apr 28, 2015I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
      RETURNS date
AS
BEGIN;
 DECLARE @Result date;
 SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
   RETURN @Result;
END;
Error In Derived Column Component
Oct 20, 2006I am getting this error,
[Derived Column [192]] Error: Converting an input column from type DT_STR to type DT_WSTR failed. An error occurred while performing the implicit conversion on the input column.
But I don really understand y there is a attempt to type cast at all ?
Please advise ....
thanks in advance
Derived Column Transformation - Error
Aug 24, 2007Can i call the FUNCTION within another FUNCTION
Like SUBSTRING(CHECK_NO,2,LEN(CHECK_NO) - 1) ???
I am reading the Check_No "1234321" from the flat file. The file holds all the value within double quote and values are sepearated by comma.
Objective: I am trying to elimiate the double quote using "Dervied Column'.
Strange: The above FUNCTION is working fine while construct the SQL Query.
Pls help me. Thank you.
Error On Derived Column Transformation
May 20, 2008
Hi all€”Given a date field called [Reading Date] and a time field called [Reading Time], I am attempting to use the following transformation on the field to test for nulls and combine the data into a single field called [Reading Date/Time]:
(DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8))
I get the following error:
Error at Data Flow Task [Derived Column1[177]]: Attempt to parse the expression "(DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed or might be missing part of a required element such as a parenthesis.
Here is a sample [Reading Date]:
05/07/08
Here is a sample [Reading Time]:
19:45:48
I need to be able to handle exceptions if one or both contain NULL or no data at all. Any suggestions on how to fix this?
Thanks,
Jon
Derived Column Error:can't Change Datatype
Aug 31, 2007I am trying to replace the value of a column in a derived column component, but it will not let me change the datatype.
It has decided that the column is a float, which is wrong.
How can I change it to the correct type?
Derived Column Task Failing With Error 0xC0049067
Apr 20, 2006I have a package that fails as soon as it hits the first Data Flow that contains a Derived Column task. The task takes three date columns and looks for a date of 6/6/2079. If it is there, it is replaced with a NULL. This task worked fine until I installed the Non-CTP version of SQL 2005 SP1, earlier today. (I went from RTM 9.0.1399 to SP1 9.0.2047)Does anyone have any ideas?
Here is the error I am trapping:
An error occurred while evaluating the function.
The "component "Update Max Date Value to NULL" (346)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "WeekEndingDate" (455)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "Update Max Date Value to NULL" (346) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0209029.
I tried to configure the error output to ignore the failure. The task succeeded. It did not insert anything, however, for the three columns in question. It then failed on the next Data Flow that contained a Derived Column task. I tried to pre-populate the columns with a default date, but no go. Any help would be appreciated.
Consuming Error Output From A Derived Column Component
Apr 16, 2007Hi,
I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?
or can anyone give me any example how to use/handle error output collection in SSIS?
I will appreciate all kind of suggestions.
thanks
Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?
Jul 28, 2015I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation
Mar 5, 2007Hi,
I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance.
When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error.
When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong.
Any help in this will sincerely be appreciated.
Thanks
Derived Column Usage When Column Does Not Exist In Source (but Exists In Destination)
Sep 11, 2007
Posted - 09/10/2007 : 15:53:26
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CM") = Month(Now)
DTSDestination("CY") = Year(Now)
DTSDestination("Comments") = DTSSource("Col031")
DTSDestination("Manufacturer") = DTSSource("Col030")
DTSDestination("Model") = DTSSource("Col029")
DTSDestination("Last Check-in Date") = DTSSource("Col028")
Main = DTSTransformStat_OK
End Function
***********************************************************
Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
thanks in advance!
jm
Is Possible To Call A VB.NET Function Within Derived Transformation Editor
Mar 5, 2008Hi,
In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis.
A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table.
Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing.
So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; -
Code Snippet
(SUBSTRING(DOB_SRC,8,2) + "/" + SUBSTRING(DOB_SRC,5,2) + "/" + SUBSTRING(DOB_SRC,1,4))
But really what I want to be able to do is to instruct the Data Analysts to do is something like; -
ConvertTextToDate(DOB_SRC)
Where I previously defined that behaviour of ConvertTextToDate as a public VB.NET function.
Can someone please help. I'm pretty certain I'm not the only one with this type of requirement.
Thanks in advance,
Kieran.
Altering Column Values Using Derived Column Component
Dec 21, 2007Can anyone show how to alter the value in a column using DerivedColumn component when creating an SSIS package programatically.
View 4 Replies View RelatedDerive A Column Based On Other Derived Column In A Table .. Is It Possible ?
Mar 25, 2008
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
360-Day Calendar Year Function...
Jun 23, 2008I'm looking for a function that will return an INTEGER that computes the number of days between two dates based on a 360-calendar year instead of using just the datediff function which is actual days.
If anybody knows where I could find something like this, I would be greatly appreciated.
Does this site have a library of user-defined sql functions? Or, does somebody have a site that you know of where there are a bunch?
Thanks,
South Side Rob
Group By With Year Function
Jun 19, 2014select empid,orderdate from [Sales].[Orders] WHERE custid = 71
group by empid,orderdate
sample data resultset:
empid orderdate
1 2006-12-25
1 2007-12-24
1 2007-12-23
1 2008-12-23
1 2008-04-24
select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)
sample data resultset:
empid orderdate
1 2006
1 2007
1 2008
why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.
ISO Week Of Year Function
Jan 18, 2006This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
(
@Datedatetime
)
returnsint
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin
declare @WeekOfYearint
select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a
return @WeekOfYear
end
go
-- Execute function on first day of first week of year from 1990 to 2030
select
DT,
ISO_WEEK_OF_YEAR =
dbo.F_ISO_WEEK_OF_YEAR(a.DT)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a
CODO ERGO SUM
Error In Using Alias Column Name As Function Parameter
May 16, 2012I am working on migrating view from Ms Access to SQL server. I got a query and modified it by removing IIF by CASE WHEN. I landed into following query:
Code:
SELECT CASE WHEN <CONDITION>
THEN DATEADD(YYYY,YR1,DATEADD(D,DAY1,TXNDATE))
ELSE 0
END AS CurrentDateAdj,
Year(CurrentDateAdj) + '_' + 'some text and processing')
FROM INCREMENTDATATABLE;
Here DAY1 and YR1 are from INCREMENTDATATABLE.
I am getting error that CurrentDateAdj not found. How can I fix this?
Last Year Month To Date Function
Feb 16, 2006I 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 RelatedProblem Function Year In Sql Mobile
Aug 29, 2007Dear , all
I have a problem in fucntion year in sql mobile , Use select Date_in from Test Where Year(Date_in) = 8
Brg
Tingnong
Add Values To A Column With Derived Column Expression?
Feb 25, 2008Hi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
How Can I Get Current Year In The User Defined Function.
Nov 17, 2005I have a user defined function in datebase SQL 2000. function looks like
create function Getcurrentdate(@month int, @day int) returns smalldatetimebegin
declare date1 as smalldatetime--get current year --convert month, day and year into current date. then return
return date1end
my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"How can I get current year in the user defined function. Thanks
T-SQL (SS2K8) :: Create Function For Dynamically Update Every Year?
May 27, 2015UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.