SQL Query DateTime Data Format

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)

SQL Query DateTime Data Format

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-17 : 16:45:48

Hi,

Can someone tell me why my function does not return anything
in the case the @StartDate <> '1800-01-01'? There should be something.
The language server is: English (United States)

ALTER FUNCTION [dbo].[fn_NoteList] ( @NPANXXID CHAR(7), @StartDate DateTime, @EndDate DateTime) 
returns varchar(1024)
WITH SCHEMABINDING 
AS BEGIN

DECLARE @ReturnValue VARCHAR(1024)

If @StartDate <> '1800-01-01'

	Begin
		SELECT  @ReturnValue =  Substring(COALESCE(@ReturnValue, '') + [Subject] + ',',Len(@ReturnValue),Len(@ReturnValue)-1 )
		FROM  dbo.Note 
		WHERE     Note.CLLI = @NPANXXID and (ModifiedDate between @StartDate  and @EndDate)
	END

Else
	
	Begin
		SELECT  @ReturnValue = (Select top 1 [Subject] from (Select NoteId, CLLI, [Subject],ModifiedDate From dbo.Note group by CLLI, Subject, NoteId, ModifiedDate ) as t
		where CLLI = @NPANXXID Order by NoteId desc) 	
	END
	
	RETURN @ReturnValue   
END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-09-17 : 21:23:46

In the first query you are trying to take the len of a null value. Since returnvalue is not set yet the len(@returnvalue) will return null therefore nulling out your substring.

This will cause the @returnvalue to be returned as null.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-18 : 09:12:50

i didnt understand the purpose of substring. can you explain what you're trying to do there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-20 : 07:43:47

I found out that if I put manually the date in this format 09-17-2010 instead of 2010-09-17, it returns correctly the results.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 10:01:52

well.

1) you should use the ISO standard datetime format for strings so there is no confusion. I.e you should use '18000101' -- then SQL server cannot mistake this for another date.

2) Does this modified function provide the same results?


ALTER FUNCTION [dbo].[fn_NoteList] (
	@NPANXXID CHAR(7)
	, @StartDate DATETIME
	, @EndDate DATETIME
	) 
RETURNS VARCHAR(1024)

WITH SCHEMABINDING 

AS BEGIN

	DECLARE @ReturnValue VARCHAR(1024)


	IF @StartDate <> '18000101' BEGIN
		SELECT
			@ReturnValue = COALESCE(@ReturnValue, '') + [Subject] + ','
		FROM  dbo.Note 
		WHERE
		Note.CLLI = @NPANXXID
		AND (ModifiedDate BETWEEN @StartDate  AND @EndDate)

		SET @returnValue = LEFT(@returnValue, LEN(@returnValue) - 1)
	END

	ELSE BEGIN

			SELECT TOP 1 @returnValue = [Subject]
			FROM dbo.Note
			WHERE
				CLLI = @NPANXXID
			ORDER BY NoteId DESC 
	END

RETURN @ReturnValue   
END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-20 : 10:41:23

Hi Transact Charlie,

That works perfectly like I wish. I do not get why removing the substring in my query and replacing it with
your SET @returnValue = LEFT(@returnValue, LEN(@returnValue) - 1) does the trick. To me, both cases look the samething.
If you have more spare time to explain to me, I would very appreciate!

Anyhow, thanks a lot for helpiong me on this.:-)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 10:45:48

I did think your SUBSTRING was just to get rid of the last comma.

My way just make a little more sense because you only do it once. With your way you were performing the SUBSTRING for every row you were concatenating.

As you are using 2005 maybe you should make @returnValue into a VARCHAR(MAX)???

Or are you very sure you're never going to breach the character limit of subjects?

I'm really not 100% sure if my second change



SELECT TOP 1 @returnValue = [Subject]
FROM dbo.Note
WHERE
	CLLI = @NPANXXID
ORDER BY NoteId DESC

was analagous to the complicated one you had before -- I'd test that if I were you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-20 : 12:42:56

Hi Charlie,
I did not expect to have more then 1024 but it is a good idea to not take that chance so I made it like your suggestion to varchar(max).
Also, I tried your second change and it still works great. It is less complicated than mine and the results are as good as mine. So this is better since it is shorter.

Thanks a lot again Charlie!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources