GetBytes error when using a DATE column

Quick troubleshooting post. Given this error from a colleague upon execution of a pretty simple function that used a DATE column:

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'RelDate'.  The GetBytes function can only be used on columns of type Text, NText, or Image.

I couldn't reproduce the problem on my machine - the same function worked fine for me.  Checked all his query execution settings and didn't see anything, and then he realized he was running SQL Management Studio 2005 connected to a 2008 R2 instance.  As soon as he fired up SMS 2008 R2, the function worked fine.

Stripping time from GETDATE(), 2008 edition

Stripping the time from GETDATE() to get the date at 00:00:00 is a common practice, and has been accomplished by various methods in the past, including CASTing to VARCHAR and back, using DATEADD and DATEDIFF, and using FLOOR after casting to INT.  It should be less necessary these days since the introduction of the DATE data type, but sometimes it's still a useful comparison trick.

And now because of DATE there's a simpler way to do the conversion: CAST(@dt as DATE).  But is this any faster?

The short answer is no, but it doesn't seem to be any slower either.  I ran a quick test:

declare @dtest datetime
declare @i int = 0, @j int = 0
declare @start datetime

set @start = GETDATE()

while @i < 1000000
begin
    set @dtest = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    set @i = @i + 1
end
    
print datediff(ms, @start, getdate())
set @start = GETDATE()

while @j < 1000000
begin
    set @dtest = CAST(GETDATE() as DATE)
    set @j = @j + 1
end

print datediff(ms, @start, getdate())

In half a dozen runs, the two versions never varied by more than a handful of ms.  Feel free to try on your own and let me know if you see anything different.

DATEs aren't INTs, at least not any more

I updated a database column today to utilize the SQL 2008 DATE type, upgrading from a SMALLDATETIME which always had a 00:00:00 hh:mm:ss component. I was fairly certain that this would not break any existing code, so I did not perform an exhaustive code search. My mistake.

A function broke with the following message:

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

"Strange," I thought. "How would an int be getting used as a date?"

Of course there's the basic method of datetime construction from a string: CAST("2012-01-01" as datetime), but that doesn't involve any INTs. Did someone try to use a similar INT (20120101) in place of the string? No, because that would cause an arithmetic overflow...

Well, it turns out that there's another way of using INTs as DATETIMEs:

select cast(CAST('2012-01-10' as datetime) as int)

Apparently this doesn't work any more:

select cast(CAST('2012-01-10' as date) as int)

And this is what was being performed in the function in question:

...

CASE WHEN io_end > 40947 THEN 1 ELSE 0 END

...

Which promptly broke when I changed the column to a DATE. So let this be a warning: even something as innocuous as a DATETIME to DATE change needs QA and code search.

If your DATE comes through pyodbc as a unicode, it's probably the driver

File this under "should have been obvious." I had a test script break today because I was trying to do date math on an object that wound up being a unicode instead of a date. I had gotten the variable value from a pyodbc query against a SQL 2008 database table with a DATE column. I knew that DATETIME columns came in as datetime.datetime objects, but the DATE column came in as a unicode, which seemed strange.

Turns out it's the ODBC driver. Demo:

In [78]: cnxn = pyodbc.connect("DSN=local;")

In [79]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")

In [80]: r = crsr.fetchone()

In [81]: type(r.bar)
Out[81]: unicode

Then, when switching to SQLNCLI10:

In [96]: cnxn = pyodbc.connect("Driver={SQL Server Native Client 10.0};Server=localhost;Trusted_Connection=Yes;")

In [97]: crsr = cnxn.execute("select cast('2012-01-01' as date) as bar")

In [98]: r = crsr.fetchone()

In [99]: type(r.bar)
Out[99]: datetime.date