THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

No New DMVs In RC0 Compared to CTP6

Remember this post 46 New Dynamic Management Views In SQL Server 2008 CTP6?

I just checked SQL Server 2008 RC0 and there are no new Dynamic Management Views when compared to CTP6.

Sorry, can't make up any DMVs :-(


Now, if you could do this

SELECT CAST('00:15:00' as TIME(0)) + 5

instead of

SELECT DATEADD(hh,5,CAST('00:15:00' as TIME(0)))

Wouldn't that be nice? You can do it with datetime, for example SELECT GETDATE() + 5

Published Tuesday, June 24, 2008 4:21 PM by Denis Gobo
Filed under: ,



Scott R. said:


Thanks for the feedback on the RC0 DMVs.

An interesting set of date/time examples!

For your example query:

SELECT CAST('00:15:00' as TIME(0)) + 5

The error message is:

Operand type clash: time is incompatible with int

This refers to data type incompatibilities (time versus int), but does not mention overloaded operator incompatibilities for the + operator (perhaps it stops evaluating for errors after hitting the first error).

I modified the sample query to:

SELECT CAST('00:15:00' as TIME(0)) + CAST('05:00:00' as TIME(0))

to make both operands of the same data type and attempt to add 15 minutes and 5 hours (as in your DateAdd example), and received a different error message:

Operand data type time is invalid for add operator.

This error suggests that the + operator is not overloaded for the new Time(0) data type, despite the fact that the + operator appears to be overloaded for the DateTime data type being returned by GetDate (with the 5 being implicitly interpreted as number of days to add to the current date).

Assuming that CAST('00:15:00' as TIME(0)) + 5 did work, what implicit time unit measure should be applied to the 5 operand?

- Hours

- Minutes

- Seconds

- Days (24 hour periods)

- Something else?

Based on the GetDate() + 1 interpreting the 5 as days, I might guess that the 5 is implicitly interpreted as the lowest whole (non-fractional) time unit, which would be seconds.

I guess that while DateAdd is less straightforward to use, it has the advantage of explicitly choosing the time unit measure and quantity to add (the "hh, 5" being 5 hours).

The .NET framework has a data type called TimeSpan which represents a time interval (an amount of time between two date / time values, in days, hours, minutes, seconds, and fractions of seconds).  If I recall correctly, you can add or subtract a date/time and an internal (TimeSpan), resulting in a DateTime (such as your example of time 0:15:00 and interval 05:00:00 resulting in time 05:15:00).  Or you can subtract two date/time values resulting in an interval value.  But you can't add two date/time values together (incompatible data types).  Unfortunately, there is no equivalent to TimeSpan as a native SQL Server data type (even in SQL 2008).

Scott R.

June 24, 2008 7:19 PM

Jason said:

You can use the gettime() function to do this but you have to put in the cheat code. up up down down left right left right b a

June 25, 2008 12:44 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement