SQL Server Performance

Insert maximum value of datetime datatype as defau

Discussion in 'General Developer Questions' started by pakiboy, Aug 9, 2006.

Page 1 of 3

  1. pakiboy New Member

    I need help with inserting maximum value of datetime datatype in a column as default.
    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

    pakiboy, Aug 9, 2006
    #2

  2. Bredsox New Member

    What about GETDATE()? It will insert the CURRENT datetime (or you can format) for the default value.

    Use DatabaseName
    GO

    CREATE DEFAULT df_EndDate AS GETDATE()
    GO

    EXEC sp_bindefault df_EndDate, "dbo.TableName.EndDate"
    GO


    Dillig
    Bredsox, Aug 9, 2006
    #2

  3. pakiboy New Member

    I dont need the current datetime. I need to insert the maximum value of datetime datatype supported by SQL 2000 as default.
    pakiboy, Aug 9, 2006
    #2

  4. Adriaan New Member

    Books OnLine is your friend ...

    On the index, look for "datetime", click on "overview".
    You get multiple options, select the one for Transact-SQL Reference: "datetime and smalldatetime".
    Adriaan, Aug 9, 2006
    #2

  5. Adriaan New Member

    And by the way - as long as the column is nullable, then you could use NULL as "no known end date".

    Just as long as you know how to use NULL in search criteria ...
    Adriaan, Aug 9, 2006
    #2

  6. pakiboy New Member

    adrian thats a good idea but unfortunately my system architect doesnt want me to have null values and then handle them. He want me to populate the column with the maximum datetime value supported by sql 2000. I can insert it explicitly by putting the value in the tsql clause but i was wondering if i could just have it as a default function and have been unsuccessful so far.Though i can just default it explicity to December 31, 9999 that is supported by sql 2000.
    Thanks for the help though
    pakiboy, Aug 9, 2006
    #2

  7. Bredsox New Member

    quote:Originally posted by pakiboy

    adrian thats a good idea but unfortunately my system architect doesnt want me to have null values and then handle them. He want me to populate the column with the maximum datetime value supported by sql 2000. I can insert it explicitly by putting the value in the tsql clause but i was wondering if i could just have it as a default function and have been unsuccessful so far.Though i can just default it explicity to December 31, 9999 that is supported by sql 2000.
    Thanks for the help though


    In that case, you can define the default and set the DEFAULT as 12/31/9999.?


    Dillig
    Bredsox, Aug 9, 2006
    #2

  8. SQL_Guess New Member

    Sounds like this may be what you want:



    Use DatabaseName
    GO

    CREATE DEFAULT df_EndDate AS '9999-12-31 23:59:59.990' --< this is the max datetime value, per BOL
    GO

    EXEC sp_bindefault df_EndDate, "dbo.TableName.EndDate"
    GO


    Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 9, 2006
    #2

  9. aefager New Member

    But then he will have to come back and fix it when it becomes the Y10K problem! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
    aefager, Aug 9, 2006
    #2

  10. Bredsox New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by aefager</i><br /><br />But then he will have to come back and fix it when it becomes the Y10K problem! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />All of us won't live that long so someone will have to.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Dillig
    Bredsox, Aug 9, 2006
    #2

  11. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><pre id="code"><font face="courier" size="2" id="code">CREATE DEFAULT df_EndDate AS '9999-12-31 23:59:59.990' --&lt; this is the max datetime value, per BOL<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It doesn't really make a huge difference, but this statement is slightly incorrect. The max datetime value is '9999-12-31 23:59:59.99<b>7</b>'. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 9, 2006
    #2

  12. Adriaan New Member

    I guess this system architect person doesn't understand the pointlessness of a 'stop value' for any column. Either that, or they're still in a dBase 4 frame of mind.
    Adriaan, Aug 10, 2006
    #2

  13. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><pre id="code"><font face="courier" size="2" id="code">CREATE DEFAULT df_EndDate AS '9999-12-31 23:59:59.990' --&lt; this is the max datetime value, per BOL<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It doesn't really make a huge difference, but this statement is slightly incorrect. The max datetime value is '9999-12-31 23:59:59.99<b>7</b>'. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />thanks frank. I tried with 999 initially, which error, so I based the 990 on something I saw in BOL. Checking again now, I found a different page that mentions, as you have, that 997 would be the largest value not to be rounded.<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 10, 2006
    #2

  14. pakiboy New Member

    Thank you guys. Problem Solved.
    pakiboy, Aug 10, 2006
    #2

  15. SQL_Guess New Member

    Did you use 'Create Default', or solve in another manner?

    Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 10, 2006
    #2

  16. Bredsox New Member

    quote:Originally posted by pakiboy

    Thank you guys. Problem Solved.



    It is expected that you mention which method you used to solve the problem so that other people might also benefit from this with similar problems. Did you use create default?


    Dillig
    Bredsox, Aug 10, 2006
    #2

  17. Madhivanan Moderator

    If the column is to store some default value other column current date, then why dont you use NULL?
    It is very easy to handle NULL. What type of difficulties do you face if you use NULL?

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Aug 10, 2006
    #2

  18. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />It is very easy to handle NULL. What type of difficulties do you face if you use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />There are people that think that NULLs add complexity to the logic of a query and are not that easy to handle. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Personally I tend to think that one should use a DEFAULT value instead of value whereever applicable. Why would you want to use NULL?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 10, 2006
    #2

  19. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Why would you want to use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Because that is the only way to model the UNKNOWN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
    Roji. P. Thomas, Aug 11, 2006
    #2

  20. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Why would you want to use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Because that is the only way to model the UNKNOWN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Not quite. A blank string can be a distinguishing mark, and by definition a NULL is not.
    Adriaan, Aug 11, 2006
    #2

  21. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Why would you want to use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Because that is the only way to model the UNKNOWN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Correct, but that's why I mentioned to use a DEFAULT <b>whereever applicable</b>. If such a DEFAULT however conflicts with your logic, then the only way is to use the NULL marker.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 11, 2006
    #2

  22. SQL_Guess New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Why would you want to use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Because that is the only way to model the UNKNOWN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Not quite. A blank string can be a distinguishing mark, and by definition a NULL is not.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I'm not sure I understand your comment, Adriaan. A 'blank string' is not unknown. It is known to be a blank string, and as such can be used in comparisons.<br />i.e. where '' = '' is TRUE<br />where NULL = NULL is false<br /><br />for example:<br />select TOP 1 1 FROM ATable WHERE '' = ''<br />select TOP 1 1 FROM ATable WHERE NULL = NULL<br /><br />This problem becomes worse when dealing with numeric data, since the numeric default is 0, but zero very ccertainly is NOT = unknown.<br /><br />If you are doing aggregation's, then the problem of using default values for unknown's can be exacerbated. I know you'll be aware of this simple example - I'm posting it for other who may follow the thread.<br /><br />for example:<br />SET NOCOUNT ON <br />DECLARE @ATTABLE (i dec(5,2))<br />INSERT INTO @AT (i)<br />SELECT 1<br />UNION ALLSELECT 0<br />UNION ALLSELECT 1<br />UNION ALLSELECT 0<br />UNION ALLSELECT NULL<br /><br />SELECT avg(i) FROM @AT<br /><br />average = 0.5<br /><br />whereas if those 0 were intended to mean unknown, then the average would be 1.0<br /><br />Similarly, '9999-12-31 23:59:59.997 (thanks Frank [8D]) is not an unknown date, merely a date that has not occurred yet.<br /><br />just my 2cents.<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 11, 2006
    #2

  23. Adriaan New Member

    I think the original problem was with a system architect who did not want NULL on a column, and preferred to see Dec 31, 9999.

    This person seems to be concerned with a technical non-issue of wasting space for a NULL. Well, you won't win any space by recording Dec 31, 9999 instead of NULL.

    And if you take things very strict, "Dec 31, 9999" is an outright lie.
    Adriaan, Aug 11, 2006
    #2

  24. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />Why would you want to use NULL?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Because that is the only way to model the UNKNOWN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Not quite. A blank string can be a distinguishing mark, and by definition a NULL is not.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />BALNK &lt;&gt; NULL<br />BLANK = BLANK<br />NULL &lt;&gt; NULL<br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
    Roji. P. Thomas, Aug 11, 2006
    #2

  25. SQL_Guess New Member

    quote:Originally posted by Adriaan

    ...

    And if you take things very strict, "Dec 31, 9999" is an outright lie.

    completely agree. For example:

    OrderDeliveryDate is NULL
    vs.
    OrderDeliveryDate = "Dec 31, 9999"



    Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 11, 2006
    #2

  26. Adriaan New Member

    SQL_Guess - we were posting at the same time ...

    The comment about blank strings comes from experience with composite keys, for instance when you have (MainKey, SubKey) where SubKey can be blank.
    Adriaan, Aug 11, 2006
    #2

  27. Adriaan New Member

    Roji, perhaps I should have said: by <b>syntax</b> definition.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
    Adriaan, Aug 11, 2006
    #2

  28. FrankKalis Moderator

    To add to the confusion (and kind of inconsistent threatment of NULL)<br /><br />Why must we write<br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE table SET column <b>=</b> NULL<br /></font id="code"></pre id="code"><br />instead of<br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE table SET column <b>TO</b> NULL<br /></font id="code"></pre id="code"><br />But cannot write<br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE column = NULL<br /></font id="code"></pre id="code"><br />instead of <br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE column IS NULL<br /></font id="code"></pre id="code"><br /><br />Questions over questions... [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 11, 2006
    #2

  29. Adriaan New Member

    Well Frank, I am already amazed that people get as far as this forum, yet do not know about the IS NULL syntax. Imagine what would happen if they added TO NULL ...

    But while they're at it, why not allow dynamic column names without having to resort to dynamic SQL? Yep, it's OOP time! And here's me thinking it was time for the Friday Funnies ...
    Adriaan, Aug 11, 2006
    #2

  30. Roji. P. Thomas New Member

    quote:Originally posted by FrankKalis

    To add to the confusion (and kind of inconsistent threatment of NULL)
    ....

    SUM(NULLBLE Column)
    Vs
    Variable + Variable



    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Aug 11, 2006
    #2

  31. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 11, 2006
    #2

  32. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    Well Frank, I am already amazed that people get as far as this forum, yet do not know about the IS NULL syntax. Imagine what would happen if they added TO NULL ...

    Even on a Friday evening, Assignment and comparison are two different operators.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Aug 11, 2006
    #2

  33. FrankKalis Moderator

    quote:
    Assignment and comparison are two different operators
    Absolutely correct, but at the same time it's useless to speculate about what would have been better and why. Things are as they are in SQL and we have to live with that.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Aug 11, 2006
    #2

  34. Adriaan New Member

    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by Adriaan

    Well Frank, I am already amazed that people get as far as this forum, yet do not know about the IS NULL syntax. Imagine what would happen if they added TO NULL ...

    Even on a Friday evening, Assignment and comparison are two different operators.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com

    Perhaps you mean two different operations? They do of course use the same operator.
    Adriaan, Aug 11, 2006
    #2

  35. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Even on a Friday evening...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hey Adriaan, in Rojis place its already evening and after a long and hard day of work...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 11, 2006
    #2

  36. Adriaan New Member

    I wouln't expect MVPs such as yourselves to make any mistake whatsoever.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
    Adriaan, Aug 11, 2006
    #2

  37. FrankKalis Moderator

    Hm, didn't I mean that we're correct by definition? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Here's one of the few jokes about MVPs:<br /><br />Q: How many MVP does it take to change a light-bulb?<br />A: 6<br /><br />1 to hold the light-bulb<br />4 to turn the table around<br />and 1 to report the bug<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Aug 11, 2006
    #2

  38. Adriaan New Member

    Bug, beetle - I didn't know there were more than 4? Okay, not counting the late Billy Preston.

    Have I lost you there?
    Adriaan, Aug 11, 2006
    #2

  39. FrankKalis Moderator

    ??? Who is Bill Preston???

    Should we continue in "The lighter side"...?

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Aug 11, 2006
    #2

  40. Adriaan New Member

    Beetle, Beatle ...
    Adriaan, Aug 11, 2006
    #2

  41. SQL_Guess New Member

    at least we're off of the ever-contentious 'nulls debate'

    Panic, Chaos, Disorder ... my work here is done --unknown
    SQL_Guess, Aug 11, 2006
    #2
Page 1 of 3

Share This Page