SQLTeam.com | Weblogs | Forums

Store Procedure issue


#1

ALTER PROCEDURE [dbo].[Time_Value_Change]
@TimeValue varchar(500)
AS
BEGIN
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),
--EndDate NVARCHAR(MAX))

--insert into #LocalTempTable values
--(
--@xxx,
--@yyy
--)

select StartDate,EndDate from @Temp

END

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


#2

please share the CREATE TABLE statement for the table Filter_DatePredicate


#3

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.


#4

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


#5

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


#6

Hi

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

Query

-- 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)


Results