|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date Only Data TypeI would like to have a field in a table that is a Date only data type instead
of the datetime. Could someone offer any advice on this in SQL Server 2005. Thanks No such thing.
http://www.aspfaq.com/2206 Show quote "Primera" <Primera@newsgroups.nospam> wrote in message news:40b7475e341e8c7e4db4ba7da06@msnews.microsoft.com... >I would like to have a field in a table that is a Date only data type >instead of the datetime. Could someone offer any advice on this in SQL >Server 2005. > > Thanks > > >> I would like to have a field [sic] in a table that is a DATE only data type instead of the DATETIME. Could someone offer any advice on this in SQL Server 2005. << You might want to learn the basics of RDBMS, so that you do not confusefields and columns. Now, to answer your question: do it the right way! Time is not a point (Chronons), but a duration expressed as half-open intervals. Thus a day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the ISO temporal model. Read Rick Snodgrass at Univeristy of AZ for more details. Again, you show your complete lack of development experience.
I'll quote one example because for you thats all I'm bothered doing. Consider financial systems, yes you have a trade date (and time), but you also have a settlement date; that settlement date is a date that does not need a time, it is not correct to say 12 Jan 2005 00:00:00 because that would indicated the trade was settled at midnight, instead it needs to be just 12 Jan 2005 because the trade needs to settle sometime on Jan 12. Go out and get a junior programming job and get yourself some needed industrial and programming experience and you won't have fundemental problems like this again. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137015747.137256.202240@f14g2000cwb.googlegroups.com... >>> I would like to have a field [sic] in a table that is a DATE only data >>> type instead of the DATETIME. Could someone offer any advice on this >>> in SQL Server 2005. << > > You might want to learn the basics of RDBMS, so that you do not confuse > fields and columns. > > Now, to answer your question: do it the right way! Time is not a point > (Chronons), but a duration expressed as half-open intervals. Thus a > day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the > ISO temporal model. > > Read Rick Snodgrass at Univeristy of AZ for more details. > > Consider financial systems, yes you have a trade date (and time), but you Thanks Tony. I did not follow one point however. 00:00:00 is in Jan 12, so > also have a settlement date; that settlement date is a date that does not > need a time, it is not correct to say 12 Jan 2005 00:00:00 because that > would indicated the trade was settled at midnight, instead it needs to be > just 12 Jan 2005 because the trade needs to settle sometime on Jan 12. why would it matter if that is the settlement time even if you did not track the actual time? Also, I would think they would actually want/need to capture the exact settlement time. If for no other reason then audit history if a dispute arises and they need to outline that time period to see what happened. -- William Stacey [MVP] William Stacey [MVP] (william.sta***@gmail.com) writes:
> Thanks Tony. I did not follow one point however. 00:00:00 is in Jan It's a waste of four bytes. Per such column. Per each row. Our table > 12, so why would it matter if that is the settlement time even if you > did not track the actual time? for contract notes has six date-only columns. A customer who has been running our system for a while and that has some trading volume, easily have ten million rows. That's 240 MB of zeroes. > Also, I would think they would actually want/need to capture the exact You don't know the finance business very well, do you? You make a trade > settlement time. on the stock market today. With a normal settlement scheme, this means that this trade is to be settled on Tuesday 17 Jan. That is the agreement. But the agreement does not specify whether you are to pay at 09:00 or at 10:15. And the settlement date is put on the contract note the day the note is created. (Which normally is the trade date.) > If for no other reason then audit history if a dispute arises and they For auditing, yes, the timestamp with hours and minutes for the actual> need to outline that time period to see what happened. registration may be of interest. It is also of interest to register the actual settledate (sometimes payment or delivery is late), but that again is a date. And all this goes back to a fundamental thing in banking: you get interest per day, not per hour. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi William,
I think Erland covers my point well. Another example is date of birth, it is not correct to have a time unless you are storing the date of birth including time. Consider the situation where somebody uses datetime for date of birth, they set it to midnight; then, a different application starts using your database table and starts storing the time of birth in the date of birth, you have one system storing it as midnight by default and another the actual time; now, an user wants to do some analysis on this, say a distribution analysis of what time people are born - the default midnight ones should be immediately discounted, but how? You don't know they are incorrect! Going back to finance because thats where most of my current experience comes from, most trading systems i've used store dates as integer in the form 20060112, now, what on earth use is that! Doing date arithmetic is not straight forward. Tony. Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:uGgqSX3FGHA.1816@TK2MSFTNGP11.phx.gbl... >> Consider financial systems, yes you have a trade date (and time), but you >> also have a settlement date; that settlement date is a date that does not >> need a time, it is not correct to say 12 Jan 2005 00:00:00 because that >> would indicated the trade was settled at midnight, instead it needs to be >> just 12 Jan 2005 because the trade needs to settle sometime on Jan 12. > > Thanks Tony. I did not follow one point however. 00:00:00 is in Jan 12, > so why would it matter if that is the settlement time even if you did not > track the actual time? Also, I would think they would actually want/need > to capture the exact settlement time. If for no other reason then audit > history if a dispute arises and they need to outline that time period to > see what happened. > > -- > William Stacey [MVP] > > > > Going back to finance because thats where most of my current experience Never mind validating to prevent nonsense dates from getting in there in the > comes from, most trading systems i've used store dates as integer in the > form 20060112, now, what on earth use is that! Doing date arithmetic is > not straight forward. first place. It just adds more weight to the argument of having a pure DATE type.
This is why many financial systems use integer, because a) less storage and b) less overhead on inserting and updating rows - you don't require the check constraint which less face it adds overhead and its overhead on something you need to happen as fast as possible - trading. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eB%23UcG5FGHA.2652@tk2msftngp13.phx.gbl... >> Going back to finance because thats where most of my current experience >> comes from, most trading systems i've used store dates as integer in the >> form 20060112, now, what on earth use is that! Doing date arithmetic is >> not straight forward. > > Never mind validating to prevent nonsense dates from getting in there in > the first place. > If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database. The only time it makes a difference is if you are comparing two date values. If you always insert/update your date values without specifying the time, then the time is set to midnight of that day. As long as you are consistent, this has the same effect as if you didn't store the time at all, and you wont run into issues when comparing dates. You could build in a trigger that insures the time value is set to 00:00:00 on inserts and updates as well, which would guarantee consistency even if the application programming mistakenly sets the time to another value. The only other real difference is storage, and I doubt it is significant enough to even give consideration to with today's storage costs. Show quote "Primera" <Primera@newsgroups.nospam> wrote in message news:40b7475e341e8c7e4db4ba7da06@msnews.microsoft.com... > I would like to have a field in a table that is a Date only data type instead > of the datetime. Could someone offer any advice on this in SQL Server 2005. > > Thanks > > > The only other real difference is storage, and I doubt it is significant Well, I would suggest that if you only care about the date, then using > enough to even give consideration to with today's storage costs. SMALLDATETIME will minimize storage requirements and make indexes more efficient, and I don't know of any downside... A The downside is that you can't store any date prior to January 1, 1900 or
after June 6, 2079. Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uEG3WvvFGHA.1452@TK2MSFTNGP11.phx.gbl... > >> The only other real difference is storage, and I doubt it is significant >> enough to even give consideration to with today's storage costs. > > Well, I would suggest that if you only care about the date, then using > SMALLDATETIME will minimize storage requirements and make indexes more > efficient, and I don't know of any downside... > > A > > I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result. However, a Date only type can be mentally easier to deal with as you know time can not effect what your doing. I actually added a TDate and TTime UDTs to a sql project I did. Can use it as is or update it as needed if you don't like the api choices. Get the project at link below. http://channel9.msdn.com/ShowPost.aspx?PostID=147390 -- Show quoteWilliam Stacey [MVP] "Primera" <Primera@newsgroups.nospam> wrote in message news:40b7475e341e8c7e4db4ba7da06@msnews.microsoft.com... >I would like to have a field in a table that is a Date only data type >instead of the datetime. Could someone offer any advice on this in SQL >Server 2005. > > Thanks > > William Stacey [MVP] (william.sta***@gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as I don't think so. People have been screaming for this for many years,and I would very disappointed if it is not in the next version of SQL Server. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of the more profound use cases for Date only you have come across? TIA Erland. -- Show quoteWilliam Stacey [MVP] "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns974953149317Yazorman@127.0.0.1... > William Stacey [MVP] (william.sta***@gmail.com) writes: >> I would agree with others that a Date only type has only marginal value >> as > > I don't think so. People have been screaming for this for many years, > and I would very disappointed if it is not in the next version of SQL > Server. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > With 2005, they don't have to wait, unless they want to. Why? The Date and Time datatypes were dropped from the product very early on. I think most of it was due to the volume with which we complained about its implementation (and by we I do mean myself, Erland and others). I'm sure that's not what you mean, so while, yes, you could create your own UDT, have fun with that, and let us know when you have something marginally useful! The most complex UDT I've seen to date that was actually useable was POINT. There is so much involved with date validation and interoperability that it is unlikely you would be able to develop something that would seamlessly integrate with the rest of the product (most notably implicit conversion to/from datetime and acceptance as inputs to functions like DATEDIFF/DATEADD/YEAR/MONTH/DAY). > What are some of the more profound use cases for Date only you have come A calendar table. Hire/fire date. Birth date. I'm sure if I spent more > across? than two minutes and reviewed all of the projects I've been involved with in the past 10 years, I could come up with dozens of others. None of these need time in most cases, and life would be much simpler if we didn't have to truncate/validate/correct data going in or coming out, or when comparing, or when displaying, or when exporting to XML, or ... > so while, yes, you could create your own UDT, I did one a couple weeks ago and posted a few times. It may or may not be useful for all, but I would appreciate any feedback. Not sure how close it may be to the one they dropped as I was not in the beta. Here is some of the stuff you can do from TSQL:> have fun with that, and let us know when you have something marginally > useful! http://channel9.msdn.com/ShowPost.aspx?PostID=147390 -- Create TDate via implicit conversion. declare @td TDate declare @td2 TDate declare @dt datetime set @td = '1/1/2005' -- Implicit conversion from string. set @td2 = '1/2/2005' set @dt = '1/1/2005' set @td2 = TDate::FromSqlDateTime(@dt) -- Explicit conversion from sql datetime. select @td.Month as Month, @td.Day as Day, @td.Year as Year select @dt as dt, @td.ToString() as td, @td.ToLongDateString() as LongString, @td2.ToString() as td2 -- Create TDate via static methods. declare @sd3 TDate declare @sd4 TDate set @sd3 = TDate::FromDateParts(2005, 2, 1) set @sd4 = TDate::FromSqlDateTime(@dt) set @sd4 = TDate::Parse('1/1/2005') select @sd3.ToString() as sd3, @sd4.ToString() as sd4, TDate::MinValue.ToString() as MinDate, TDate::MaxValue.ToString() as MaxDate select TDate::Now.ToString() as Now select TDate::GetEndOfMonth(TDate::Now).ToString() as EndOfThisMonth, TDate::GetEndOfWeek(TDate::Now).ToString() as EndOfThisWeek, TDate::GetEndOfYear(TDate::Now).ToString() as EndOfThisYear, TDate::GetStartOfMonth('12/31/2005').ToString() as StartOfMonth -- Date math. select @td.ToString() as Orig, @td.AddDays(-1).ToString() as [Subtract 1 Day], @td.AddMonths(1).ToString() as [Add 1 Month], @td.AddYears(2).ToString() as [Add 2 Years] select @td.EndOfMonth().ToString() as EndOfMonth, @td.EndOfYear().ToString() as EndOfYear, @td.EndOfWeek().ToString() as EndOfWeek select @td.Subtract('1/2/2005').ToString() as Diff --Returns a TTimeSpan -- Date Comparision. if ( @td.IsBetween('1/1/2005', '1/2/2005') = 'true' ) print 'td is Between the dates.' else print 'td is not between the dates.' if ( @td < '2/1/2005' ) print 'td is less then date.' else print 'td is not less then date.' DECLARE @tmp table( BDay TDate NOT NULL ); insert into @tmp values('1/1/2005') insert into @tmp values('1/2/2005') insert into @tmp values('1/15/2005') -- Use Sql's Between operator. select BDay.ToString() as DatesInRange from @tmp where BDay between '1/1/2005' and '1/3/2005' -- Use Types IsBetween() method. select BDay.ToString() as DatesInRange2 from @tmp where BDay.IsBetween('1/1/2005', '1/3/2005') = 'true' > The most complex UDT I've seen to date that was actually useable As you say, there is still some issues with UDTs. No overloading is big, which also means no overloaded operators or conversions. Some of this can be done with workarounds, but it is not very clean. As far as calling DateDiff/Add, etc, all those things are on the UDT already, so no need to call another library. That said, you can always get the SqlDateTime property and pass to anything that takes a datetime. Let me know. Cheers!> was POINT. There is so much involved with date validation and > interoperability that it is unlikely you would be able to develop something > that would seamlessly integrate with the rest of the product (most notably > implicit conversion to/from datetime and acceptance as inputs to functions > like DATEDIFF/DATEADD/YEAR/MONTH/DAY). -- William Stacey [MVP] nearly every date used in the most critical date calculations for the
financial/insurance databases i've worked on for the last several years would benefit from being date-only, e.g. birth date, hire date, effective/termination dates (contract, coverage, plans, accounts, etc.), payment dates, simple calendar dates, etc. William Stacey [MVP] wrote: Show quote > With 2005, they don't have to wait, unless they want to. I agree it has > some value, but maybe I missed a whole bunch of use cases. What are some of > the more profound use cases for Date only you have come across? TIA Erland. > yeh. I was thinking in terms of things that would not work with time at
zero, but I retract the question. Indeed this would be very useful. If you get a chance, test drive my TDate udt. Thanks Trey. -- Show quoteWilliam Stacey [MVP] "Trey Walpole" <treypole@newsgroups.nospam> wrote in message news:eREl19wFGHA.2896@TK2MSFTNGP10.phx.gbl... > nearly every date used in the most critical date calculations for the > financial/insurance databases i've worked on for the last several years > would benefit from being date-only, e.g. > birth date, hire date, effective/termination dates (contract, coverage, > plans, accounts, etc.), payment dates, simple calendar dates, etc. > > William Stacey [MVP] wrote: >> With 2005, they don't have to wait, unless they want to. I agree it has >> some value, but maybe I missed a whole bunch of use cases. What are some >> of the more profound use cases for Date only you have come across? TIA >> Erland. >> William Stacey [MVP] (william.sta***@gmail.com) writes:
> With 2005, they don't have to wait, unless they want to. I agree it has With the exception of auditing columns, about all our datetime columns> some value, but maybe I missed a whole bunch of use cases. What are > some of the more profound use cases for Date only you have come across? are date-only. In fact we have a user-defined type, that has a rule bound to it which enforces the time portion to be 0. Since we use this used-defined type in most places, it would be possible for us to switch to real date-only type in SQL Server - provided that it is 100% compatible with the existing datetime. The CLR Date type that was in beta 1 would have been useless for us. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx How was the Date type in beta1 implemented. Did it not just wrap a
SqlDateTime struct? Mine just wraps a native datetime, so it would be compatible as well. -- Show quoteWilliam Stacey [MVP] "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9749637814A10Yazorman@127.0.0.1... > William Stacey [MVP] (william.sta***@gmail.com) writes: >> With 2005, they don't have to wait, unless they want to. I agree it has >> some value, but maybe I missed a whole bunch of use cases. What are >> some of the more profound use cases for Date only you have come across? > > With the exception of auditing columns, about all our datetime columns > are date-only. In fact we have a user-defined type, that has a rule bound > to it which enforces the time portion to be 0. > > Since we use this used-defined type in most places, it would be possible > for us to switch to real date-only type in SQL Server - provided that it > is 100% compatible with the existing datetime. The CLR Date type that was > in beta 1 would have been useless for us. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx William Stacey [MVP] (william.sta***@gmail.com) writes:
> How was the Date type in beta1 implemented. Did it not just wrap a I seriously doubt that. Does this work?> SqlDateTime struct? Mine just wraps a native datetime, so it would be > compatible as well. DECLARE @d yourdatetype SELECT @d = dateadd(DAY, 3, getdate()) Or if I say, SELECT @d will a legacy client see a datetime value? Or just a binary value? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > DECLARE @d yourdatetype This does not work as current UDTs do not allow implicit conversion to > SELECT @d = dateadd(DAY, 3, getdate()) another type unless it is from string (which actually just calls the Parse() method of the type). However, you can do: declare @d TDate select @d = TDate::Now.AddDays(3) This is one of advantages of types, they have the logic to operate over they own data. If you want a native DateTime instance from a TDate, just get the "SqlDateTime" property. > SELECT @d This is still an "issue" with UDTs IMO. AFAIC, Select returns the binary value for all types (even for datetime). It is the query output window (or your app) that is doing the convertion to string for display, which I guess it does automatically for known types, but not for UDTs, unless I missed an optional switch. If you want to see the string, you would do "select @d.ToString()" > will a legacy client see a datetime value? Or just a binary value? A client will see the Type. So you work with TDate on client and server. You could, I guess, parse the binary value yourself into a datetime if don't want to load the TDate type for some reason. The binary wire format is same as sql datetime. -- William Stacey [MVP] William Stacey [MVP] (william.sta***@gmail.com) writes:
>> DECLARE @d yourdatetype And thus the compatibility that I demand is not available.>> SELECT @d = dateadd(DAY, 3, getdate()) > > This does not work as current UDTs Your types may be good for new apps, but don't tout them as compatible with datetime, as they aren't. > declare @d TDate To be perfectly honest: syntatic sugar.> select @d = TDate::Now.AddDays(3) > > This is one of advantages of types, they have the logic to operate over > they own data. > This is still an "issue" with UDTs IMO. AFAIC, Select returns the A .Net client will if the assembly is loaded. So you can get the proper> binary value for all types (even for datetime). It is the query output > window (or your app) that is doing the convertion to string for display, > which I guess it does automatically for known types, but not for UDTs, > unless I missed an optional switch. display of your type in Mgmt Studio, or at least so I've understood it. I haven't tried it, though. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > And thus the compatibility that I demand is not available. Huh? Even if you had a native "date" type tomorrow, you don't get it for free. You still would need to change column types (ugg) and refactor code most likely. You want to be able to add a new type to a complex system and not have to change anything? Good luck with that. Most would not do that anyway. Why change something that works already. The value is having a Date type going forward. > Your types may be good for new apps, but don't tout them as compatible I was not touting anything. What I said is correct. The range and storage > with datetime, as they aren't. is the same because it is stored as a datetime. Moreover, you can change between them, just not implicitly. So you can still do below: declare @d datetime declare @td TDate set @d = getdate() set @td = TDate::FromSqlDateTime(@d) -- From native datetime. select @td.ToString() set @d = @td.SqlDateTime -- To native datetime. select @d And if your problem domain is "Date", then you should not have to be doing a bunch of converstions anyway. Everything can input as a Date, process as a Date, and output as a Date. So DateTime should not have to come into the picture for the most part. But if it does, you can convert to and from. > Sorry. What part is syntatic sugar? You can point to most anything and say >> declare @d TDate >> select @d = TDate::Now.AddDays(3) >> >> This is one of advantages of types, they have the logic to operate over >> they own data. > > To be perfectly honest: syntatic sugar. it is an abstraction of something lower. > On the client side it will naturally. However, if it works in MgmtStudio, I >> This is still an "issue" with UDTs IMO. AFAIC, Select returns the >> binary value for all types (even for datetime). It is the query output >> window (or your app) that is doing the convertion to string for display, >> which I guess it does automatically for known types, but not for UDTs, >> unless I missed an optional switch. > A .Net client will if the assembly is loaded. So you can get the proper > display of your type in Mgmt Studio, or at least so I've understood it. > I haven't tried it, though. would be gratefull to know the solution if anyone steps on it. Cheers. -- William Stacey [MVP] "William Stacey [MVP]" wrote: When Microsoft added the bigint data type in SQL2K, everyone correctly> > > And thus the compatibility that I demand is not available. > > Huh? Even if you had a native "date" type tomorrow, you don't get it for > free. You still would need to change column types (ugg) and refactor code > most likely. You want to be able to add a new type to a complex system and > not have to change anything? Good luck with that. Most would not do that > anyway. Why change something that works already. The value is having a Date > type going forward. [snip] assumed that an existing int column could be changed to bigint without "refactoring" code. And after making sure that there are no out-of-bound values, changing an int to a smallint was/is just as easy. So if we had a native date data type tomorrow, then a datetime/smalldatetime column with only true dates (with time 00:00:00.000) could be changed to date without changing one line of code. Microsoft would make sure the date data type would cast to a datetime implicitely, just as a smallint implicitely converts to an int when needed. A UDT date data type is not of the same family, would (probably) not support the same implicit castings, existing functions (such as DATEADD and DATEDIFF), etc. I would have loved it if Microsoft had finally added a native date data type. IMO, a UDT version really isn't the same thing... Gert-Jan > When Microsoft added the bigint data type in SQL2K, everyone correctly Yeh, but to change your existing column types is not a small thing. And you > assumed that an existing int column could be changed to bigint without > "refactoring" code. And after making sure that there are no out-of-bound > values, changing an int to a smallint was/is just as easy. still have to reason about things run today because it is a datetime, but may not after a switch to Date. In most cases, the conversion would be smooth, but I would not bet it would be without any code changes. > So if we had a native date data type tomorrow, then a That would also work today with UDTs if they only had implemented calling > datetime/smalldatetime column with only true dates (with time > 00:00:00.000) could be changed to date without changing one line of > code. Microsoft would make sure the date data type would cast to a > datetime implicitely, just as a smallint implicitely converts to an int > when needed. the implicit conversion methods on UDT types. My class implements the implicit conversion from sqldatetime, but it is ignored by sql. Otherwise implicit casts would work as expected. I am unclear why they did not implement this import function as it seems like such an obvious need. >A UDT date data type is not of the same family, would (probably) not As they add types (such as time and date), now we begin to see problem with >support the same implicit castings, existing functions (such as DATEADD >and DATEDIFF), etc. I would have loved it if Microsoft had finally added >a native date data type. IMO, a UDT version really isn't the same using same APIs such as DateAdd and DateDiff. They where built for datetime *only with their parms as such. So you have options on the api that will not make sense for a date or a time. So to make it clean, they need to add different apis - then you get into api spaghetti. The operators/methods belong on the types themselfs. That is the only way to make it clean and elegant going forward with new types. -- William Stacey [MVP] "William Stacey [MVP]" wrote: Yes, you would have to retest just to make sure, but if all datetime> > > When Microsoft added the bigint data type in SQL2K, everyone correctly > > assumed that an existing int column could be changed to bigint without > > "refactoring" code. And after making sure that there are no out-of-bound > > values, changing an int to a smallint was/is just as easy. > > Yeh, but to change your existing column types is not a small thing. And you > still have to reason about things run today because it is a datetime, but > may not after a switch to Date. In most cases, the conversion would be > smooth, but I would not bet it would be without any code changes. functions were supported, it is unlikely that code would have to be changed if the goal from the start was to use a date (not a datetime). > > So if we had a native date data type tomorrow, then a Indeed, a missed opportunity.> > datetime/smalldatetime column with only true dates (with time > > 00:00:00.000) could be changed to date without changing one line of > > code. Microsoft would make sure the date data type would cast to a > > datetime implicitely, just as a smallint implicitely converts to an int > > when needed. > > That would also work today with UDTs if they only had implemented calling > the implicit conversion methods on UDT types. My class implements the > implicit conversion from sqldatetime, but it is ignored by sql. Otherwise > implicit casts would work as expected. I am unclear why they did not > implement this import function as it seems like such an obvious need. > >A UDT date data type is not of the same family, would (probably) not Although this is true, it is not a problem at all. One would not change> >support the same implicit castings, existing functions (such as DATEADD > >and DATEDIFF), etc. I would have loved it if Microsoft had finally added > >a native date data type. IMO, a UDT version really isn't the same > > As they add types (such as time and date), now we begin to see problem with > using same APIs such as DateAdd and DateDiff. They where built for datetime > *only with their parms as such. So you have options on the api that will > not make sense for a date or a time. a datetime to a date if time functions were used on it. By the way: instead of time date type I would prefer a somewhat more useful duration datetype... > So to make it clean, they need to add different apis - then you get into api spaghetti. I disagree. The APIs are fine, only some parameters would not besupported or act as a no-op. > The operators/methods belong on the types themselfs. That is the only way to make it clean and That is a different view and a different programming model. I like that> elegant going forward with new types. model very much, but it is no reason to abandon the old model. What Microsoft could have done for the system functions is call the matching method on the UDT. For example, if the user submitted the query "SELECT DATEADD(day,3,MyDateColumn)", then SQL Server could call the DateAdd method on the date object for MyDateColumn. Would that not be the best of both worlds? Gert-Jan Show quote > -- > William Stacey [MVP] > Although this is true, it is not a problem at all. One would not change Agree. But DATEADD(month, x) does not make sense on a Time, nor does > a datetime to a date if time functions were used on it. DATEADD(hour) on a Date. Yeh they could throw an exception, but man, that seems very hacky to me. They could also ignore, but that is probably worse then throwing an exception. > By the way: instead of time date type I would prefer a somewhat more I agree. A TimeSpan type. I did a clr version, but agree it should be > useful duration datetype... native. > I disagree. The APIs are fine, only some parameters would not be I guess we disagree. Apis should not take options that could never work in > supported or act as a no-op. the first place. This will just lean to confusion and is not good api design IMHO. If one just really likes using "DateAdd" (i.e. loves the name) I suppose the other cleaner option would be to support overloades on functions and type methods - which they really need anyway. Then you would also need different Enums depending on the type. > That is a different view and a different programming model. I like that I agree it is different. But I fear as new types are introduced, it is the > model very much, but it is no reason to abandon the old model. best way to make sense of it all in a logical fasion and not have a flat function space growing wild. Plus consistensy would be nice. As UDTs will have properties and methods to do their magic, as should native types. Then you don't need the flat static functions any longer (except for things that don't make sense on any particular type). > What Microsoft could have done for the system functions is call the They could. Its not a bad idea. When they stand back and look at things in > matching > method on the UDT. For example, if the user submitted the query "SELECT > DATEADD(day,3,MyDateColumn)", then SQL Server could call the DateAdd > method on the date object for MyDateColumn. Would that not be the best > of both worlds? total, not sure it would hold up for all cases and the framework in general, but maybe. It could definitely be used as a glide-path. Thanks for the discussion! Cheers. -- William Stacey [MVP]
Show quote
"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message Perhaps more "unachievable" than "missed". For temporal types,news:43C7FD9D.998CA433@toomuchspamalready.nl... > "William Stacey [MVP]" wrote: >> >> >> That would also work today with UDTs if they only had implemented calling >> the implicit conversion methods on UDT types. My class implements the >> implicit conversion from sqldatetime, but it is ignored by sql. >> Otherwise >> implicit casts would work as expected. I am unclear why they did not >> implement this import function as it seems like such an obvious need. > > Indeed, a missed opportunity. > implicit conversion would need to involve strings as well, and I can only guess that deciding how to implicitly convert '03/04/05' to a date would be a real mess for CLR types. For a new DATE type to be useful, '03/04/05' would have to convert implicitly to the same DATE and DATETIME value. If someone changes their Windows regional settings, you don't want the implicit conversion to DATE to change (because it relies on some Windows .dll), while the implicit conversion to DATETIME to stay the same. And if the programmer writes SET DATEFORMAT dmy, you don't want DATETIME to change behavior but not date. I assume the CLR types you want to wrap have behaviors that depend on the system locale, so you'd have quite a bit of work to do to make these types behave like SQL native types. Even worse would be to change all the rules for SQL native types and have them suddenly depend far more strongly on external settings. I still haven't tried out William's TDate type, but when I see TDate::Parse('11/13/2005').AddDays(3) in the test script, I'm not confident that Parse will interpret '11/13/2005' in the same way as CAST does. And since TDate::Parse uses System.DateTime.Parse, not System.Data. SQLTypes.SqlDateTime.Parse, I wonder what TDate::Parse('10/10/1111') does. I don't mean to suggest William should have gotten every detail right, but I want to point out that he didn't (and shouldn't be expected to) write detailed specifications of every possible use of TDate. But unless one does, it's a mistake to think the result will be robust and reliable. Show quote >> >A UDT date data type is not of the same family, would (probably) not What if Microsoft simply implemented as much more of the SQL>> >support the same implicit castings, existing functions (such as DATEADD >> >and DATEDIFF), etc. I would have loved it if Microsoft had finally added >> >a native date data type. IMO, a UDT version really isn't the same >> >> As they add types (such as time and date), now we begin to see problem >> with >> using same APIs such as DateAdd and DateDiff. They where built for >> datetime >> *only with their parms as such. So you have options on the api that will >> not make sense for a date or a time. > > Although this is true, it is not a problem at all. One would not change > a datetime to a date if time functions were used on it. > > By the way: instead of time date type I would prefer a somewhat more > useful duration datetype... standard as possible? DATE, TIMESTAMP WITH TIMEZONE, INTERVAL... These are all part of the standard, and their semantics are already worked out. I'm not saying the implementation would be easy, but it would lead to useful types with few surprises. Show quote > Perhaps, if Microsoft took the care to write specifications before writing>> So to make it clean, they need to add different apis - then you get into >> api spaghetti. > > I disagree. The APIs are fine, only some parameters would not be > supported or act as a no-op. > >> The operators/methods belong on the types themselfs. That is the only >> way to make it clean and >> elegant going forward with new types. > > That is a different view and a different programming model. I like that > model very much, but it is no reason to abandon the old model. What > Microsoft could have done for the system functions is call the matching > method on the UDT. For example, if the user submitted the query "SELECT > DATEADD(day,3,MyDateColumn)", then SQL Server could call the DateAdd > method on the date object for MyDateColumn. Would that not be the best > of both worlds? the implementation. The type of the expression DATEADD(day,3,X) must depend only on the type of X. Do you want it to be DATE if X is of type DATE? If so, do you also want DATEADD(second,3,X) to be of type DATE? What do you want to be the result of DATEADD(hour,12,DATEADD(day,1,X)) ? When signatures are different for date and datetime, how should '2005-11-01' be implicitly converted? How about '2005-11-01 00:00:00'? My point is that all of this is really hard, and if you really care about data integrity and persistence, foundations of database work, and you also want to use an expressive language like SQL, which demands incredible attention to semantics, you absolutely have to work upward from solid principles and specifications (SQL standards and native types), not downward from something useful you see lying around (.NET types) that work well and have reasonable semantics, but in a very different context (applications programming with an imperative, object-oriented language). SK Show quote > > Gert-Jan > > >> -- >> William Stacey [MVP] > Perhaps more "unachievable" than "missed". For temporal types, Actually, that is the 1 conversion that actually works today. They actually > implicit conversion would need to involve strings as well, > and I can only guess that deciding how to implicitly > convert '03/04/05' to a date would be a real mess for CLR types. call the Parse() method on the type and pass the string. So you can do: set @d = '1/1/2005' > For a new DATE type to be useful, '03/04/05' would have to Not sure I follow. They both convert to same date/time. Naturally a type > convert implicitly to the same DATE and DATETIME value. If > someone changes their Windows regional settings, you don't > want the implicit conversion to DATE to change (because it > relies on some Windows .dll), while the implicit conversion to > DATETIME to stay the same. could screw up their parse method. > I still haven't tried out William's TDate type, but when I see Throws an out of range exception. I could replace to using > TDate::Parse('11/13/2005').AddDays(3) > in the test script, I'm not confident that Parse will interpret > '11/13/2005' in the same way as CAST does. And since > TDate::Parse uses System.DateTime.Parse, not System.Data. > SQLTypes.SqlDateTime.Parse, I wonder > what TDate::Parse('10/10/1111') does. sqldateTime.parse(). I wonder too what differences there may be. It should be stated, if I did not, this is beta/test code. So please forward any errors or poor api design choices. >My point is that all of this is really hard, and if you really care I think we can all get behind that. What matters is the results are correct >about data integrity and persistence, foundations of database work, >and you also want to use an expressive language like SQL, which >demands incredible attention to semantics, you absolutely have to >work upward from solid principles and specifications (SQL standards >and native types), not downward from something useful you see lying >around (.NET types) that work well and have reasonable semantics, >but in a very different context (applications programming with an >imperative, object-oriented language). the API is clean and makes sense. The tools or frameworks they use to accomplish that end result don't really matter. Cheers. -- William Stacey [MVP] BTW - I should have used sqldatetime.parse() instead. I expect I will find
a few more of these issues. Thanks! -- William Stacey [MVP] Well I guess any range in datetime that was out of range for sqldatetime
would have been caught in the constructor as we turn around and create a sqldatetime from the datetime, but using sqldatetime in the parse is still the right thing. -- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:%23tbKAuIGGHA.1032@TK2MSFTNGP15.phx.gbl... > BTW - I should have used sqldatetime.parse() instead. I expect I will > find a few more of these issues. Thanks! > > -- > William Stacey [MVP] > > "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message The m/d/yyyy format better not be the only one that works, since it'snews:uE%23wWoIGGHA.1424@TK2MSFTNGP12.phx.gbl... >> Perhaps more "unachievable" than "missed". For temporal types, >> implicit conversion would need to involve strings as well, >> and I can only guess that deciding how to implicitly >> convert '03/04/05' to a date would be a real mess for CLR types. > > Actually, that is the 1 conversion that actually works today. They > actually call the Parse() method on the type and pass the string. > So you can do: > set @d = '1/1/2005' not universal. Aren't the .NET Parse() routines culture-aware? If they are, then '03/04/05' should be March in the US, April in Italy, and May in Croatia. I don't see how Parse can react to a SQL SET LANGUAGE statement like CAST does: set language us_english select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') set language Italiano select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') set language hrvatski select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') set language us_english -- Results (three different dates) /* Changed language setting to us_english. 2005-03-04 00:00:00.000 L'impostazione della lingua è stata sostituita con Italiano. 2005-04-03 00:00:00.000 Changed language setting to hrvatski. 2003-04-05 00:00:00.000 Changed language setting to us_english. */ > See above. The result should depend on the language setting>> For a new DATE type to be useful, '03/04/05' would have to >> convert implicitly to the same DATE and DATETIME value. If >> someone changes their Windows regional settings, you don't >> want the implicit conversion to DATE to change (because it >> relies on some Windows .dll), while the implicit conversion to >> DATETIME to stay the same. > > Not sure I follow. They both convert to same date/time. Naturally a type > could screw up their parse method. of the SQL connection if it is to be compatible with T-SQL datetime, and I doubt it is. Show quote > Yup. Unfortunately, "results are correct" is very hard to write good tests>> I still haven't tried out William's TDate type, but when I see >> TDate::Parse('11/13/2005').AddDays(3) >> in the test script, I'm not confident that Parse will interpret >> '11/13/2005' in the same way as CAST does. And since >> TDate::Parse uses System.DateTime.Parse, not System.Data. >> SQLTypes.SqlDateTime.Parse, I wonder >> what TDate::Parse('10/10/1111') does. > > Throws an out of range exception. I could replace to using > sqldateTime.parse(). I wonder too what differences there may be. It > should be stated, if I did not, this is beta/test code. So please forward > any errors or poor api design choices. > >>My point is that all of this is really hard, and if you really care >>about data integrity and persistence, foundations of database work, >>and you also want to use an expressive language like SQL, which >>demands incredible attention to semantics, you absolutely have to >>work upward from solid principles and specifications (SQL standards >>and native types), not downward from something useful you see lying >>around (.NET types) that work well and have reasonable semantics, >>but in a very different context (applications programming with an >>imperative, object-oriented language). > > I think we can all get behind that. What matters is the results are > correct the API is clean and makes sense. The tools or frameworks they > use to accomplish that end result don't really matter. Cheers. for in a typical database application, where because of the nature of SQL, you may not be able to control where things like literal strings are interpreted or implicitly converted (client, server, other server, provider?). SK Show quote > > -- > William Stacey [MVP] > > > | set language us_english Yeh, there is a bunch of culture globalization stuff you can do. I don't | select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') | set language Italiano | select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') | set language hrvatski know, however, if you can query the sql language state from within a UDT. If you could, then you could grab it and use it accordingly. If not, another way would be to set a static member on the UDT. Seems like they need a current SqlSession class that any UDTs could enumerate. -- William Stacey [MVP] I suppose you could also query @@language within the UDT? But would you
have to query this each time in Parse() as this could change throughout the session? -- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:%23eAFPyJGGHA.3176@TK2MSFTNGP12.phx.gbl... || set language us_english || select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') || set language Italiano || select cast('03/04/05' as datetime)--, TDate::Parse('03/04/05') || set language hrvatski | | Yeh, there is a bunch of culture globalization stuff you can do. I don't | know, however, if you can query the sql language state from within a UDT. | If you could, then you could grab it and use it accordingly. If not, | another way would be to set a static member on the UDT. Seems like they | need a current SqlSession class that any UDTs could enumerate. | | -- | William Stacey [MVP] | | | William Stacey [MVP] (william.sta***@gmail.com) writes:
> I suppose you could also query @@language within the UDT? But would you The one to check for numeric dates is DBCC USEROPTIONS, and the dateformat> have to query this each time in Parse() as this could change throughout > the session? column. But then you could just as well use CAST() to perform the conversion... -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx William Stacey [MVP] (william.sta***@gmail.com) writes:
>> When Microsoft added the bigint data type in SQL2K, everyone correctly To change to a Date type that is not compatible with datetime, I would>> assumed that an existing int column could be changed to bigint without >> "refactoring" code. And after making sure that there are no out-of-bound >> values, changing an int to a smallint was/is just as easy. > > Yeh, but to change your existing column types is not a small thing. And > you still have to reason about things run today because it is a > datetime, but may not after a switch to Date. In most cases, the > conversion would be smooth, but I would not bet it would be without any > code changes. have to: 1) Change and reload all tables. 2) Revise a whole lot of code. To change to a Date type that works just like datetime I would have to do only the first. And while that is not a neglible amount of work, it is a realistic option. 2) is not when you have 3700 stored procedures. > Agree. But DATEADD(month, x) does not make sense on a Time, nor does Depends on what you mean with sense.> DATEADD(hour) on a Date. DECLARE @d date ... SELECT @d = dateadd(Hour, 1, @d) makes as much as sense: DECLARE @i int ... SELECT @i = @i + 0.1 which is perfectly possible to do today. Essentially, the return type of dateadd that uses a non-day datepart, would be a datetime expression when the third parameter is a date value. The case with Time is a little trickier, since there is no other fractional type that can be used as pattern. But if the conversion rule from datetime to Time is that the date portion is chopped off, there is no doubt how dateadd(month, 1, @time) would work. It is of course ugly if dateadd() returns different date types depending on datepart and the third parameter, but since the datepart is static, the typing is also static. Ugly, and I would not put it in a new product. (Could not dateadd() always return datetime? Nope. Say that you have: SELECT .. FROM tbl WHERE datecol = dateadd(DAY, 29, @datevar) if dateadd returns datetime, datecol will be converted to datetime, which precludes the use of the index. I am here assuming that Date will be below datetime in the data-type precedence.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx William Stacey [MVP] (william.sta***@gmail.com) writes:
>> And thus the compatibility that I demand is not available. Ideally, it would be a matter of checking out and changing one single file, > > Huh? Even if you had a native "date" type tomorrow, you don't get it for > free. You still would need to change column types (ugg) and refactor code > most likely. the file that defines the type aba_date. In reality, we still have some legacy code that uses datetime or smalldatetime. But it's still doable. We could of course have to reload a whole bunch of tables in our customers' production environments. That's another story. >> Your types may be good for new apps, but don't tout them as compatible Which is a major issue, particularly when it comes to use with>> with datetime, as they aren't. > > I was not touting anything. What I said is correct. The range and > > storage is the same because it is stored as a datetime. Moreover, you > can change between them, just not implicitly. the datetime functions. There is nothing wrong with implementing a Date type in CLR, and while you are at it, you can develop a new Datetime as well. For new applications, it may be the best things since sliced bread. But just don't imagine that it's close to be useful for existing applications. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Which is a major issue, particularly when it comes to use with That is our main difference here. You would not need the datetime functions > the datetime functions. anyway. You use the functions right on the types, which are easier to reason about anyway. I know, I know, you don't agree - and that is ok. As I sit here, I can't help but think this is similar to the issue VB users had/have with VB.Net. In the end, most of them warmed up to the framework way of thinking. > There is nothing wrong with implementing a Date type in CLR, and while That is already in the lib. TDate, TTime, TDateTime, TTimeSpan, TDateRange, > you are at it, you can develop a new Datetime as well.\ TString, TStopWatch, StringList (i.e. string array), IntList Check it out some time and provide feedback if possible. -- William Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message William,news:OtI9uI9FGHA.644@TK2MSFTNGP09.phx.gbl... > > That is already in the lib. TDate, TTime, TDateTime, TTimeSpan, > TDateRange, TString, TStopWatch, StringList (i.e. string array), IntList > Check it out some time and provide feedback if possible. VS isn't my cup of tea, but here's what I got when I tried (and failed) to deploy what I pulled down from the sandbox. Can you help out? Warning 1 'SqlUtils.TStopwatch' overrides Object.Equals(object o) but does not override Object.GetHashCode() C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TStopWatch.cs 12 19 SqlServerProject1 Warning 2 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 371 39 SqlServerProject1 Warning 3 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 376 39 SqlServerProject1 Warning 4 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 274 13 SqlServerProject1 Warning 5 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 274 31 SqlServerProject1 Warning 6 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 284 13 SqlServerProject1 Warning 7 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use System.Runtime.InteropServices.ComTypes.FILETIME instead. http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs 284 31 SqlServerProject1 Warning 8 The private field 'SqlUtils.TStopwatch.tickFrequency' is assigned but its value is never used C:\Documents and Settings\Steve\My Documents\Visual Studio 2005\Projects\SqlServerProject1\SqlServerProject1\TStopWatch.cs 20 40 SqlServerProject1 Error 9 CREATE ASSEMBLY failed because type "NativeMethods" in safe assembly "SqlServerProject1" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies. SqlServerProject1 Show quote > > -- > William Stacey [MVP] > > > Hi Steve. I should have added some more instructions. As I only had to do
this once, I did not think about it. 1) I would unclick warnings on the build error list. Just to keep them from distracting you. Some of those are ~normal. 2) The project properties must not carry over fully on a fresh load - such as connection string and Permission Level. Right-click the SqlUtils project and click Properties. In the Properties Tab, click Database. Make sure Permission Level is "Unsafe". This is needed by an assembly that does Win32 calls (which I needed for some of the Timezone stuff.). Also check the connection string is pointed at your DB of interest. 3) If you have not done so already, you need to enable clr integration at the server: sp_configure 'clr enabled', 1 GO RECONFIGURE GOI think that is it and it should then deploy. Let me know if you get another error. Cheers! -- Show quoteWilliam Stacey [MVP] "Steve Kass" <sk***@drew.edu> wrote in message news:%23fDgtgBGGHA.376@TK2MSFTNGP12.phx.gbl... > > "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message > news:OtI9uI9FGHA.644@TK2MSFTNGP09.phx.gbl... >> >> That is already in the lib. TDate, TTime, TDateTime, TTimeSpan, >> TDateRange, TString, TStopWatch, StringList (i.e. string array), IntList >> Check it out some time and provide feedback if possible. > > William, > > VS isn't my cup of tea, but here's what I got when I tried (and > failed) to deploy what I pulled down from the sandbox. Can you > help out? > > Warning 1 'SqlUtils.TStopwatch' overrides Object.Equals(object o) but does > not override Object.GetHashCode() C:\Documents and Settings\Steve\My > Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TStopWatch.cs 12 19 > SqlServerProject1 > Warning 2 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 371 39 SqlServerProject1 > Warning 3 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 376 39 SqlServerProject1 > Warning 4 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 274 13 SqlServerProject1 > Warning 5 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 274 31 SqlServerProject1 > Warning 6 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 284 13 SqlServerProject1 > Warning 7 'System.Runtime.InteropServices.FILETIME' is obsolete: 'Use > System.Runtime.InteropServices.ComTypes.FILETIME instead. > http://go.microsoft.com/fwlink/?linkid=14202' C:\Documents and > Settings\Steve\My Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TimeZoneInformation.cs > 284 31 SqlServerProject1 > Warning 8 The private field 'SqlUtils.TStopwatch.tickFrequency' is > assigned but its value is never used C:\Documents and Settings\Steve\My > Documents\Visual Studio > 2005\Projects\SqlServerProject1\SqlServerProject1\TStopWatch.cs 20 40 > SqlServerProject1 > Error 9 CREATE ASSEMBLY failed because type "NativeMethods" in safe > assembly "SqlServerProject1" has a pinvokeimpl method. P/Invoke is not > allowed in safe assemblies. SqlServerProject1 > > > >> >> -- >> William Stacey [MVP] >> >> >> > > Erland Sommarskog <esq***@sommarskog.se> wrote in
news:Xns9749BF64750AYazorman@127.0.0.1: And where Erland has the guy "touted" his data type as totally SQL> Your types may be good for new apps, but don't tout them as compatible > with datetime, as they aren't. compatible? > Oh come on Erland, give him a break - we can almost call any higher>> declare @d TDate >> select @d = TDate::Now.AddDays(3) >> >> This is one of advantages of types, they have the logic to operate >> over they own data. > > To be perfectly honest: syntatic sugar. > level functionality for syntactic sugar. What is it with you T-SQL guys and Date data types anyway? As soon as someone pipes up with alternatives you guys come down on them like a ton of bricks. Yes, I too want a true native Date type (and Time for that matter) and I too can see that what we had in Beta 1 and subsequent tries are not compatible, but come on... It's getting like Mac vs. Windows etc :-) BTW, I may see you tomorrow if you're in the office - I may pop in. Niels -- ************************************************** * Niels Berglund * http://staff.develop.com/nielsb * nielsb@no-spam.develop.com * "A First Look at SQL Server 2005 for Developers" * http://www.awprofessional.com/title/0321180593 ************************************************** Niels Berglund (nie***@develop.com) writes:
> And where Erland has the guy "touted" his data type as totally SQL William said "Mine just wraps a native datetime, so it would be > compatible? compatible as well." Which it soon proved that it wasn't. > Oh come on Erland, give him a break - we can almost call any higher Sugar usually adds taste to things, so it's probably a nice thing. But> level functionality for syntactic sugar. What is it with you T-SQL guys > and Date data types anyway? As soon as someone pipes up with > alternatives you guys come down on them like a ton of bricks. Yes, I too > want a true native Date type (and Time for that matter) and I too can > see that what we had in Beta 1 and subsequent tries are not compatible, > but come on... It's getting like Mac vs. Windows etc :-) I did not see anything that added something truly new in terms of functionality. > BTW, I may see you tomorrow if you're in the office - I may pop in. Ah, we could have a date!-- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > William said "Mine just wraps a native datetime, so it would be It is binary compatible. We both already knew you can't pass a UDT to a > compatible as well." Which it soon proved that it wasn't. DateADD yet. Until they implement calling implicit converstion methods. > Sugar usually adds taste to things, so it's probably a nice thing. But huh? How can you say that. Did you even look at it? It has got a lot of > I did not see anything that added something truly new in terms of > functionality. stuff there to make working with dates a lot easier IMO. But I can tell we will never meet minds on this. Different strokes makes wonderous diversity :-) Cheers. -- William Stacey [MVP] |
|||||||||||||||||||||||
Other interesting topics