SQLTeam.com | Weblogs | Forums

Store Procedure issue

ALTER PROCEDURE [dbo].[Time_Value_Change]
@TimeValue varchar(500)
declare @xxx Nvarchar(MAX) ='';
declare @yyy Nvarchar(MAX) ='';
declare @Qry NVARCHAR(MAX) = '';
declare @Qry1 NVARCHAR(MAX) = ''
set @Qry= 'select @x = Start_Value FROM Filter_DatePredicate where Predicate_Name = '''+@TimeValue+'''';
set @Qry1= 'select @y = End_Value FROM Filter_DatePredicate where Predicate_Name = '''+@TimeValue+''''

declare @xx NVARCHAR(MAX) ;
set @xx = '';
declare @yy NVARCHAR(MAX) ;
set @yy = '';

EXEC sp_executesql @Qry,N'@x Nvarchar(MAX) out', @xx out;

set @xxx = 'select '+@xx

Exec (@xxx)

EXEC sp_executesql @Qry1,N'@y Nvarchar(MAX) out', @yy out;

set @yyy= 'select '+@yy

Exec (@yyy)

Declare @Temp table (StartDate Nvarchar (MAX),EndDate Nvarchar (MAX))

insert into @Temp(StartDate,EndDate) values(@xxx,@yyy)

-- CREATE TABLE #LocalTempTable(
--StartDate NVARCHAR(MAX),

--insert into #LocalTempTable values

select StartDate,EndDate from @Temp


i want date in date format which is storeed in table in coverted format and access in c#

i am getting value in @xxx and @yyy but when i store it in temp table and get it from there again i am getting in converted format

start value in table is CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101)

and end value is CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())),101)

predicate_name=Cur Mth

please share the CREATE TABLE statement for the table Filter_DatePredicate

Why are you doing all of these dynamic queries?
Why do you have dates as varchars in @temp table
StartDate Nvarchar (MAX),EndDate Nvarchar (MAX))

Please provide sample data and the final result you would like to see.

1 Like

What you want to do looks very simple. Your approach is complicated.

Start_Value in Filter_DatePredicate CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101) i want it in datetime format like


This is what I got
Hope it helps
Please let me know


-- Start_Value in Filter_DatePredicate
SELECT CONVERT(VARCHAR(25), Dateadd(dd, -( Day(Getdate()) - 1 ), Getdate()), 101

---i want it in datetime format like 2017-08-08
SELECT CONVERT(VARCHAR(19), Getdate(), 120)