Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: date replacing in SELECT statement

    In the following simple select statement:

    Code:
    SELECT     Selskabsnummer, Plademærkenummer, [Fra dato], isnull([Til dato], getdate()) as [Til dato], Bemærkning
    FROM         Gramex_DW.dbo.Plademærkelinie WITH (NOLOCK)
    WHERE     convert(int, Plademærkenummer) = 1
    ORDER BY isnull([Til dato], getdate()) ASC
    I'm looking for a way to replace the [Fra dato] field with '1900-01-01' if the date earlier than this date. E.g. instances of 1800-01-01 exist.

    I'm not looking for an update to the table, but merely a way to manipulate the output, as the front-end I'm developing can't handle pre 1900-01-01 date formats, and I would prefer formatting the data before it reaches he front-end as it would be easier to handle.

    /Trin

  2. #2
    Join Date
    Oct 2005
    Posts
    183
    to be more specific, I essentially need to convert the datetime value of the [Fra dato] to smalldatetime. But as smalldatetime can't handle '1800-01-01' using the convert functon would give me an overflow error. So I need to work around it somehow.

  3. #3
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    try to use case funtion

  4. #4
    Join Date
    Oct 2005
    Posts
    183
    I doubt that'll help, as the convertering (whether using convert or cast) to smalldatetime will overflow.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use CASE as suggested not CAST

    declare @d1 datetime
    set @d1='1800-02-01'
    select d1= case
    when @d1 < '1900-01-01' then convert(smalldatetime,'1900-01-01')
    else convert(smalldatetime,@d1)
    end

  6. #6
    Join Date
    Oct 2005
    Posts
    183
    lol... case... of course, lol... someone borrow me a pair of glasses.

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    Blindingly simple.. solution:

    Code:
    SELECT     Selskabsnummer, Plademærkenummer, isnull([Til dato], getdate()) as [Til dato], Bemærkning, 
    	CASE 
    		WHEN [Fra dato] < '1900-01-01' Then '1900-01-01'
    		ELSE [Fra dato]
    	END as [Fra dato]
    FROM         Gramex_DW.dbo.Plademærkelinie WITH (NOLOCK)
    WHERE     convert(int, Plademærkenummer) = 1
    ORDER BY isnull([Til dato], getdate()) ASC

Posting Permissions

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