Hi I was trying to convert a scalar function in db to c#.
In db function was like this:This converts frequency to date for e.g
if frequency is monthly : 'M' and input is "2005 M1" (which is [Date] column), then it converts to 01-01-2005
CREATE FUNCTION [dbo].[funcFreqToDate]
(
@Freq varchar(50),
@InputVal varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
.
IF @Freq = 'D'
RETURN @InputVal;
IF @Freq = 'A'
RETURN @InputVal +'-01-01';
DECLARE @FreqToDate varchar(255)
DECLARE @FreqInfo varchar(255);
DECLARE @YearInfo varchar(255);
DECLARE @FreqPos int;
SET @FreqPos = CHARINDEX(@Freq,@InputVal);
IF @FreqPos > 0
SET @FreqInfo = SUBSTRING(@InputVal,(@FreqPos+1),LEN(@InputVal));
SET @YearInfo = SUBSTRING(@InputVal,1,(@FreqPos-1))
SET @FreqToDate =
CASE
WHEN @Freq='M' THEN @YearInfo +'-'+ cast((cast(@FreqInfo AS INT)) AS varchar(50)) + '-01'
ELSE @InputVal
END
RETURN @FreqToDate
It was called in c# as
query = @"UPDATE " + srcDataTbl + @"
SET [DateTransfrm] = CAST([dbo].funcFreqToDate([Frequency],[Date]) AS DATE)";
Please remember [Date] is varchar
[DateTransfrm] is of type Date
No i want to remove scalar function completely from db and move it into query in c#.
Can anyone help me write query for same in c#.
Thanks