Post reply

Insert maximum value of datetime datatype as default

  • August 9, 2006 at 9:23 am

    #173764

    I have a table with column Enddate defined with datetime datatype. I want to insert the maximum value of datetime datatype as default when no value is provided for that column. I cant seem to figure out how to determine the maximum value of datetime datatype programmatically or by tsql rather than hard coding it in the insert command.

    Thanks

    Umair Iqbal

  • August 9, 2006 at 10:23 am

    #654129

    add a default constraint with udf ie. UDF will return the max datatime from the same table.

  • August 10, 2006 at 8:27 am

    #654329

    My guess is you were wanting to get something like this:

    select max(getdate())

    to work.  I was unable to find something that would work like that...but I did see that the valid datetime / smalldatetime is:

    datetime

    Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

    smalldatetime

    Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

    I doubt it helped - good luck

    C

    -- Cory

  • August 10, 2006 at 9:29 am

    #654350

    Cory that info you provided was neat;

    here's an example of what happens with that date:

    create table #test(id int identity,dt datetime)

    insert into #test(dt) values('12/11/9999')

    insert into #test(dt) values('12/11/9999 23:59:59')

    insert into #test(dt) values('12/11/9999 23:59:00')

    insert into #test(dt) values('12/11/9999 23:59:59:999') --rolls over the date

    insert into #test(dt) values('12/11/9999 23:59:59:997') --milliseconds accepted

    insert into #test(dt) values('12/11/9999 23:59:59:998') --rounds down

    select * from #test

    19999-12-11 00:00:00.000
    29999-12-11 23:59:59.000
    39999-12-11 23:59:00.000
    49999-12-12 00:00:00.000
    59999-12-11 23:59:59.997
    69999-12-11 23:59:59.997

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 10, 2006 at 4:38 pm

    #654448

    Learn how to use NULLs.

    It's more flexible and more effective.

    _____________
    Code for TallyGenerator

  • August 11, 2006 at 6:51 am

    #654526

    Thanks guys. Cory you were right i was looking for something like max(getdate()) but then i just wrote a function of my own to cast the value to a datetime datatype.

    Thanks to all.

  • August 11, 2006 at 2:58 pm

    #654673

    I totally agree with Sergiy on this!  A default value is vastly different than the lack of information. 

    I wasn't born stupid - I had to study.

  • August 13, 2006 at 8:27 pm

    #654727

    Here, Here!  Learn to work with NULLs on this type of thing.  There is a great value to NULLs in a column like this especially if you allow future enddating.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply