Results 1 to 10 of 10

Thread: Default Date

  1. #1
    Join Date
    Sep 2011
    Posts
    39

    Unanswered: Default Date

    I have an EndDate column with datatime2(7)..and i want it to have a default value of 9999-12-31.....but i cant find an 'endoftime' function in books online....and also when i type in that date as the default it says theres an error validating the defualt value that i entered

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE timeDemo (
       dateBegin        DATETIME        NOT NULL
       DEFAULT '1752-01-01 00:00:00.000'
    ,  dateEnd          DATETIME        NOT NULL
       DEFAULT '9999-12-31 23:59:59.997'
       )
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    39
    thanks..i didnt put quotes around the date..thats why it couldnt validate the value

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Pat,

    Isn't 1753 the first date available as datatime, not 1752 ???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct, I was flying low and I hadn't caught that typo. Good eye!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Pure coincidence . . .

    . . . this week, had an issue with a system I maintain . . . reads employee hire dates from Oracle . . . was long-ago told that I did not have to validate any of the data coming from Oracle . . . user typed-in employee hire dates of 211 instead of 2011 . . . when my system tried to read dates in the year 211, it had severe problems.

    Now I test all of my dates coming from Oracle and force it to exclude any data with dates prior to 1753.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh come now! I've worked with people (teachers) for whom a 211 hire date would be completely credible. I'm sure that they worked with quill on parchment for centuries.

    I find it kind of frightening that there are databases that don't at least offer a warning about storing data like that. Too many people don't understand dates and the problems associated with them so they would take a date like that at face value, thinking that the date could make sense.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use getdate() function for default date

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jassi.singh View Post
    Use getdate() function for default date
    so what?

    are you even reading any of the threads that you're dropping these one-line responses into?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jassi.singh View Post
    Hello,

    Use getdate() function for default date
    Jassi, will you please first read the question (and the given answers) before posting? You are spreading misinformation.

    Someone who needs a way to have the maximum datetime - SQL Server allows - as a default value, is not helped with a way to get the current date and time.

    Sorry, Rudy. I only noticed you had already responded on Jassi's post after submitting mine. But it seems we are both annoyed by the same situation.
    Last edited by Wim; 10-05-11 at 08:45.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •