Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511

    Unanswered: How to identify date 9999-01-01

    Hi,

    I have a simple query like this:

    Code:
    SELECT DISTINCT MaturityDate 
    FROM MyTable
    Let's say it returns these results:
    Code:
    MaturityDate
    2012-10-22
    2017-01-30
    2034-11-01
    9999-01-01
    Instead of returning the large date 9999-01-01, which represents something which has an infinite maturity, I would like to return the word 'Perpetual'.

    If I amend the query to this:
    Code:
    SELECT DISTINCT 
        Case When MaturityDate = '9999-01-01' 
        Then 'Perpetual' Else MaturityDate End As MaturityDate
    FROM MyTable
    I get the error 'Conversion failed when converting date and/or time from character string'. I also get the same error when trying a similar variation using DATEDIFF.

    I guess this is some sort of overflow error? Please can someone show me how to perform this comparison?

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The problem is:
    Code:
    Then 'Perpetual' Else MaturityDate
    You want to put both a STRING and a DATE in the same column. You will have to CONVERT the MaturityDate DATE to a (VAR)CHAR representation.
    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

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Ah, thank you. Following your advice, I sucessfully amended the query to:
    Code:
    SELECT DISTINCT
    Case When MaturityDate = '9999-01-01'
    Then 'Perpetual' Else CONVERT(VarChar, MaturityDate) End As MaturityDate
    FROM MyTable
    I have a quick follow-up question. I see that I could also do the conversion using CAST:
    Code:
    SELECT DISTINCT
    Case When MaturityDate = '9999-01-01'
    Then 'Perpetual' Else CAST(MaturityDate As VarChar) End As MaturityDate
    FROM MyTable
    When should I use CAST and when should I use CONVERT? Is there a difference?

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    How about using a null instead of 9999-01-01? Would be a bit cleaner in my opinion.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Thanks for the suggestion, roac - unfortunately I don't have that option.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Colin Legg View Post
    When should I use CAST and when should I use CONVERT? Is there a difference?
    CONVERT gives you control over the format (DD/MM/YYYY, YY-MM-DD, ....) of the resulting string.
    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

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's a Y10K bug in the making

Posting Permissions

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