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.


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)