Implicit Date conversion for SP Param

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 2000 Forums

Transact-SQL (2000)

Implicit Date conversion for SP Param

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 06:12:58

I've been struggling with a bug today and thought it worth passing on ...

I have an SProc which is expecting to get the date in string format. So it has something like:


CREATE PROCEDURE usp_Test1
    @MyDate varchar(24)
...

and it was being called, from another SProc (rather than from the Application) as follows:


DECLARE @dtNow datetime

SELECT @dtNow = GetDate()

EXEC usp_Test1 @MyDate = @dtNow
...

and I was struggling to work out why the date recorded by usp_Test1 was earlier than the calling Sproc (which was using @dtNow on a number of updates that all needed to be the same chronological moment in time).

Anyway, the implicit date conversion is:

Feb 21 2007 11:07AM

which clearly doesn't have any seconds, let alone milliseconds.

A trap to be aware of ...

Before you ask! : we have a need to be able to say "I don't have a value for this parameter, so leave whatever is there alone" - and there are LOTS of parameters, so adding "flag parameters" would be a pain.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-21 : 06:24:56

Is this what you mean ?


ALTER PROCEDURE usp_Test1
    @MyDate varchar(24)
as
begin
	declare	@dt	datetime

	select	@dt = @MyDate
	select	@dt as ups_Test1
end
go
DECLARE	@dtNow datetime

SELECT	@dtNow = GetDate()

EXEC usp_Test1 @MyDate = @dtNow
/*
ups_Test1                                              
------------------------------------------------------ 
2007-02-21 19:22:00.000
*/
select	@dtNow as dtNow
/*
dtNow                                                  
------------------------------------------------------ 
2007-02-21 19:22:55.147
*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 06:25:26

[code]declare @dt datetime
select @dt = getdate()
select @dt
print @dt[/code]Check out the difference between the two outputs...

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 06:30:42

Indeedie.

I still wish there was a Lint for SQL that would detect these sort of things, rather than spending hours scratching ones head!

I expect there are all sorts of horrors like this in my code which just don't happen to have been noticed :-(

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-21 : 06:39:00

Many programming languages (even T-SQL) do some testing first when converting datatypes. It tries with the "smallest" datatype available and if the data "fits", it uses that datatype.
It can easily be seen with the CHECKSUM() function. If the result is less than 32768, the resulting datatype from CHECKSUM() is SMALLINT, rather than INT.

I think the same thing happens here?
No, not really. This code

declare @dt datetime
select @dt = 90000 + getdate()
select @dt
print @dt

produces a date higher than June 6, 2079 and still only returns the minutes!

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-02-21 : 11:24:12

If nothing else the Implicit Conversion is inefficient - assuming that I have the relevant data available in the desired format already.

Just having something WARN me that I'm being a Prat would help!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources