woensdag 27 april 2011

Reset hh:mm:sss to 00:00:000 for a given datetime column

Today for testing a conversion of a system I needed to reset a datetime column to get rid of the hh:mm:sss notation.

The new system did register a time with the date like 1999-05-07 10:45:000 while the old system only registerd the date part like 1999-05-07 00:00:000

To check te conversion I only needed to know or the dates where correct meaning I had to get rid of the time notation.

First found option i tried was Cast(Convert(varchar, GetDate(),105) as datetime)
this resulted in the well known error: cannot convert varchar to datetime because it's no valid date...

I then tried Cast(Convert(varchar, GetDate(),101) as datetime) this works but is it the correct way? I think best things to do with datetime is using datetime functions so my final option is:

DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

woensdag 20 april 2011

How to drop a temporary table

What is the best way to DROP a temporay table?

The most common way I always use to drop tables:
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[TableName]') AND type = N'U')
DROP TABLE TableName

Yes, this is the standard SQL Server way and I always think that what they build is also the best practice.

When you ofcourse do this for a temporay table that you did create on DatabaseX using the above code you might find out that the table is not dropped:

USE DatabaseX
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[#tmpTableName]') AND type = N'U')
DROP TABLE #tmpTableName
GO

Why is this table not dropped, I'm sure it exists and I'm in my session you say?

The simple thing is that temporay tables are not created in you current database but always in the system database tempdb. That needs us to change the query to:

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[tmpdb]..[#TableName]') AND [type] = N'U')
DROP TABLE [#tmpTableName]
GO


Is this correct already? No it's not, yes by accident it might work but why? It might be (it happend to me) that you have a current object ID in the DatabaseX.sys.objects table that has the same ID as the #tmpTableName in the tempdb. This might be certain when you leave out the [type] where clause.

To be realy sure the statement should be:
IF EXISTS (SELECT 1 FROM tempdb.sys.objects WHERE [object_id] = OBJECT_ID(N'[tmpdb]..[#TableName]') AND [type] = N'U')
DROP TABLE [#tmpTableName]
GO

Did you see the differences?