Separate date and time datatypes - come on guys!

Hot Topic

Using SQL Server 2005 Public Keys in VB.Net
insert or update data records
Alerting on SQL Server events in SQL Server 2005
How to add reference in C# SQL Server Project (June CTP)
Installing the SQL server 2005 beta tools problem
Error 18452
Custom Rendering Extension for HTML Fields
Table Name as a parameter on Execute SQL Task?
Transformation in Data Flow Task
RS Web Service

SQL Server

Potential workaround for SP1 failure
connection error
Client-side Redirect problem
Javascript Error When trying url access
How to integrate reports into java application
Running a package in C#
Possible Concurrency Issues.
SQL Server Import Copy or Query
rsExecutionNotFound
SQL Server Project - Failed to connect to databse...

Separate date and time datatypes - come on guys!

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.

Answer this question

Separate date and time datatypes - come on guys!

  • vmadan16

    Maybe I'm missing something -- how does the fact that you have clients running other DBMSs have anything to do with the capabilities of SQL Server in this regard
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     
    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

  • Gary D

    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

  • asmana

    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.

  • GapIT

    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

  • Ted Wagner

    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.

  • Martin Skibye

    I just want to say bravo to BAnVA and give him my support

  • Liqun

    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

  • Yaachiru

    TimeSpan supports full milliseconds.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --
     
     
    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.

  • meetshums

    how difficult is it to create your data type

  • melody45

    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
    --
     
     
    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

  • Piotr Trochimiuk

    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

  • VirusFree

    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
    --
     

  • Russ Monckton

     NNTP User wrote:
    TimeSpan supports full milliseconds.
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html bID=457
    --


    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.


  • Chris Lerew

    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.
  • Separate date and time datatypes - come on guys!
    • Answer this question