Problem with SP in converting to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with SP in converting to datetime

Author

Topic

codingrocks
Starting Member

2 Posts

Posted - 2009-04-17 : 06:08:51

I have a stored procedure where I need to convert my input parameter type to a valid datetime
and return to the asp .net page.

The stored procedure is as follows ;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE PROCEDURE Search
(
	
	@EstimatedStartDate varchar(70) = default,
	
)

AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE @QUERY nvarchar(MAX)
	DECLARE @FROM nvarchar(MAX)
	DECLARE @WHERECOND nvarchar(MAX)
	SET @WHERECOND=' WHERE'

	SET @QUERY = 'SELECT CONVERT(VARCHAR(10),R.StartDate,101) ''Estimated Start Date'''
					
	SET @FROM='FROM tbl1 R'
	
IF(@EstimatedStartDate is NULL )
	BEGIN
	SET @WHERECOND  =@WHERECOND+' 1=1'
	END

DECLARE @CONDESTSTARTDATE Nvarchar(1000)
IF(@EstimatedStartDate is NULL)
BEGIN
SET @CONDESTSTARTDATE=''
END
ELSE
BEGIN

SET @CONDESTSTARTDATE= ' R.StartDate = '''+ ''+ CONVERT(VARCHAR(20),@EstimatedStartDate,101) +''''

IF(@WHERECOND<> ' WHERE')
BEGIN
SET @WHERECOND=@WHERECOND+' and '+@CONDESTSTARTDATE 
END
ELSE
BEGIN
SET @WHERECOND=@WHERECOND+@CONDESTSTARTDATE 
END
END

SET @QUERY = @QUERY + @FROM + @WHERECOND

EXEC sp_executesql @QUERY
END

If I input some date as in database with the time also, then it is giving values. But i want to get it as a date only. No time part is needed.

Can anyone please help me in fixing this.

Thanks in advance.

matty
Posting Yak Master

161 Posts

Posted - 2009-04-17 : 07:25:37

First of all, why are you using dynamic sql?
You can alter the sp as below.
ALTER PROCEDURE Search
(

@EstimatedStartDate varchar(70) = default

)
AS

DECLARE @StartDate DATETIME
BEGIN
SET NOCOUNT ON;
SET @StartDate = CONVERT(DATETIME,@EstimatedStartDate)

SELECT CONVERT(VARCHAR(10),R.StartDate,101) 'Estimated Start Date'
FROM tbl1 R
WHERE R.StartDate >= ISNULL(@StartDate,R.StartDate)
AND R.StartDate < ISNULL(@StartDate + 1,R.StartDate + 1)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:34:07

why are you making parameter as varchar if you want to pass datetime value?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-17 : 23:04:30

[code]
ALTER PROCEDURE Search
(@EstimatedStartDate DATETIME)
AS
SET NOCOUNT ON
BEGIN

SELECT CONVERT(VARCHAR(10),R.StartDate,101) 'Estimated Start Date'
FROM tbl1 R
WHERE DATEADD(dd,DATEDIFF(dd,0,R.StartDate ),0)>=DATEADD(dd,DATEDIFF(dd,0,ISNULL(@StartDate,R.StartDate)),0)
AND DATEADD(dd,DATEDIFF(dd,0,R.StartDate ),0)
<DATEADD(dd,DATEDIFF(dd,0,ISNULL(@StartDate,R.StartDate)),1)
END
SET NOCOUNT OFF[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources