Oh for crying out loud, how come there aren't separate date and time datatypes in SQL 2005! I know for a fact that this feature has been requested for YEARS!!!! Thanks for giving me the YAGNI feature of renaming my menu items in SQL Management Studio. I guess that was somehow a higher priority than a time datatype.
Separate date and time datatypes - come on guys!
RagtimeCowboy
TimeSpan supports full milliseconds.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
<monk3yatthekeyboard@discussions.microsoft.com> wrote in message news:d2fdd868-0a59-4449-9d61-70fdbc9f22fe@discussions.microsoft.com...Will the millisecond actually evaluate to a full millisecond Currently there is a rounding of the millisecond to either 0,3,7 & 9. (eg: .000, .003, .007, .009)
Ad-hoc'ing a getdate() on 2K5, it seems that this behavior is still the same. I know from an accounting perspective having a millisecond that isn't really a millisecond (due to the rounding) can be bad, mmkay.
Horst Klein
Will the millisecond actually evaluate to a full millisecond Currently there is a rounding of the millisecond to either 0,3,7 & 9. (eg: .000, .003, .007, .009)
Ad-hoc'ing a getdate() on 2K5, it seems that this behavior is still the same. I know from an accounting perspective having a millisecond that isn't really a millisecond (due to the rounding) can be bad, mmkay.
Ad-hoc'ing a getdate() on 2K5, it seems that this behavior is still the same. I know from an accounting perspective having a millisecond that isn't really a millisecond (due to the rounding) can be bad, mmkay.
Alessio Forconi
Hi Michael,
Does MS have sample code for this I have
some, but I don't think I can post it since it's for a book that's about to come out. It would be nice to be
able to point people to some MS-supplied code.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
<MRys@discussions.microsoft..com> wrote in message news:45b1a638-126e-4f9e-bed9-31b2c89b04a8@discussions.microsoft.com...Thanks Adam
Your proposal is what we currently recommend.
We are looking into providing date and time types in an upcoming version of SQL Server. Note that we have to however provide a solution that is more general since timezones are essential in the international market and the issues that I mentioned earlier are issues for which we have a usable answer. Otherwise we end up with another 20 types in future releases...
Best regards
Michael
RLovelett
I agree Time data type would be very useful -- no date association, no timezones, just points in a 24 hour period. I can work around Date by rounding off the time.
A "big" DateTime data type that goes back prior to 1753 would also be very useful. That limitation has been rather painful on a few projects. Oracle's DATE implementation would be fine.
Gregor Suttie
Thanks Adam
Your proposal is what we currently recommend.
We are looking into providing date and time types in an upcoming version of SQL Server. Note that we have to however provide a solution that is more general since timezones are essential in the international market and the issues that I mentioned earlier are issues for which we have a usable answer. Otherwise we end up with another 20 types in future releases...
Best regards
Michael
Your proposal is what we currently recommend.
We are looking into providing date and time types in an upcoming version of SQL Server. Note that we have to however provide a solution that is more general since timezones are essential in the international market and the issues that I mentioned earlier are issues for which we have a usable answer. Otherwise we end up with another 20 types in future releases...
Best regards
Michael
Alekos66
how difficult is it to create your data type
matteo raumer
I just want to say bravo to BAnVA and give him my support
SRIKANTHPRAGADA
Hi BAnVA
Thanks for your constructive feedback. We are currently looking into this. For now, this is a perfect use case for using the embedded CLR type mechanism to define your own date and time datatypes.
Now while we have your attention, could you please provide us your preferences regarding:
1. Do you want us to provide Timezones
2. Preserve timezones
3. Default non-timezone values to timezones
4. Support the year 0 or not (ISO 8601 - the date and time standard - has changed its opinion on this)
5. Are leap-seconds important for you
Your feedback is appreciated.
Best regards
Michael
Thanks for your constructive feedback. We are currently looking into this. For now, this is a perfect use case for using the embedded CLR type mechanism to define your own date and time datatypes.
Now while we have your attention, could you please provide us your preferences regarding:
1. Do you want us to provide Timezones
2. Preserve timezones
3. Default non-timezone values to timezones
4. Support the year 0 or not (ISO 8601 - the date and time standard - has changed its opinion on this)
5. Are leap-seconds important for you
Your feedback is appreciated.
Best regards
Michael
Evan H
WOW - I was pretty sure I wasn't the only one which wanted the most basic of features. CLR may be a solution for some, but some of us have clients who use mysql, oracle, informix, db2, etc. Using CLR to create a datatype is just not an option for the real world
Vinix
There is a sample which ships in the SQL Server 2005 box and also available for download at http://msdn.microsoft.com/sql/downloads/samples/default.aspx called CADateTime which demonstrates building calendar aware datetime and date datatypes using the CLR. The code could be easily modified to be just basic date or time datatypes or to model the Oracle date and time datatypes. You can learn more about how to install SQL Server 2005 samples by reading the topic called "Installing Samples" in SQL Server Books Online. After you have install these samples, the CADateTime sample typically is located at systemdrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\CADateTime. Meantime, thanks for the great suggestions for new data types.
The SQL Server Team is discussing post-2005 plans right now, and I think either new product features or samples along the lines you have requested would make a good addition to a future release. Thank you for helping us to make our products better.
--Bonnie
The SQL Server Team is discussing post-2005 plans right now, and I think either new product features or samples along the lines you have requested would make a good addition to a future release. Thank you for helping us to make our products better.
--Bonnie
sprayer
<BAnVA@discussions.microsoft.com> wrote in message news:2cbb8973-d657-4fa1-b428-929e18cc36da@discussions.microsoft.com...I'm asking for the SIMPLEST implementation possible - a date datatype that only includes the year, month, and day and a time datatype that only includes hours, minutes, seconds, and milliseconds. So that would give SQL Server a total of 4 native date-related types - datetime, smalldatetime, date, and time. The CLR should not be a factor in this implementation since it should be EASILY accomplished natively in SQL Server. I really could care less about timezones.
You can roll your own quite easily. Build a
UDT that wraps .NET's TimeSpan class. Parse() should call
TimeSpan.Parse(), ToString() should call TimeSpan.ToString(). The UDT will
need one private member, a double that can be derived from
TimeSpan.TotalMilliseconds. And you can use TimeSpan.FromMilliseconds to
reconstitute the TimeSpan when needed for ToString() or other methods you might
choose to implement. Very simple and effective as long as you don't try to
do too much with it (i.e. time zones).
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
Separate date and time datatypes - come on guys!
Separate date and time datatypes - come on guys!
aserbert
I'm asking for the SIMPLEST implementation possible - a date datatype that only includes the year, month, and day and a time datatype that only includes hours, minutes, seconds, and milliseconds. So that would give SQL Server a total of 4 native date-related types - datetime, smalldatetime, date, and time. The CLR should not be a factor in this implementation since it should be EASILY accomplished natively in SQL Server. I really could care less about timezones.
If I had to choose, I would probably pick an isolated time datatype as more critical. The following are just a few of the real-world examples where I could have used an isolated time datatype when developing a system, but had to hack a workaround.
1) Period (bell) schedule for a public school system. Dates are irrelevant as part of the schedule because students change classes at specific times depending on the day of the week. The bell schedule only has the potential for revision at the end of each year.
2) Hours of operation for a business.
3) Time range for sessions at corporate training seminars.
wangjiwei
--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html bID=457
--
Student of IT
I'm assuming you mean the TimeStamp type which is referenced as @@DBTS and is not the same as the getdate() function, nor will it work for imported data where the millisecond gets 'rounded'...
Thanks though.