THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Scott R. said:

Denis,

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

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement