Post reply

Isnull question

  • June 7, 2005 at 8:18 pm

    #362878

    I have to display string "not assigined" when a datefield is null in a table.

    I am using like ISNULL(datefiled, "not assigned"), but I am getting following error

    Syntax error converting character string to smalldatetime data type.

    Is there any way, I can acheive desired result.

    Please help

     

  • June 7, 2005 at 8:37 pm

    #563720

    There's a little catch with the isnull function. It returns the same datatype and same lenght as the input value. This also means that each paramater must be of same type/length (unless you want to have unexpected truncatations).

    Maybe this could work but I can't test it :

    case when DateField is null then 'not assigned' else DateField end.

    Please notice that the case expression will always return a string (in this case ). So you'll have to keep this in mind when you code the application.

  • June 7, 2005 at 9:00 pm

    #563721

    I tried your suggestion too, but same problem persists

    Please help

  • June 7, 2005 at 9:55 pm

    #563725

    Alpoor...

    Try this: SELECT ISNULL(CAST(datefiled AS varchar(15)), 'Not Assigned')







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 8, 2005 at 6:26 am

    #563776

    Yup, looks like you're stuck with the cast anyways :

    Declare @X as datetime

    set @X = GetDate()

    Select case when @X is null then 'some text' else @X end

    --2005-06-08 08:11:26.967

    set @X = null

    Select case when @X is null then 'some text' else CAST(@X AS Varchar(20)) end

    --some text, fails without the cast

  • June 8, 2005 at 2:36 pm

    #563990

    Thanks Susheela

    It worked like champ. thanks again

  • June 8, 2005 at 2:38 pm

    #563992

    hmm I think it's "sushila"

    .

  • June 8, 2005 at 3:04 pm

    #564003

    "Call a rose by any other name...." <;-)







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 8, 2005 at 3:08 pm

    #564005

    Maybe... but it's still your correct nickname. .

  • June 8, 2005 at 3:09 pm

    #564006

    Think anyone will take up the challenge??

    Maybe we should submit it as a question of the day .

  • June 8, 2005 at 3:24 pm

    #564007

    Know what Remi - I think you should - maybe that's the only way you'll get people to respond!

    ps: Maybe Frank will respond by morning...or Chris...or anyone else in European time!!!

    pps: Sushila isn't my nickname...it's the real one...the nickname is "Rose"..haha!!<;-)







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 8, 2005 at 4:36 pm

    #564020

    Hehe.. first time I hear a name like that!! What's the language of origin?

  • June 8, 2005 at 4:49 pm

    #564025

    C'est le pays le plus peuplé au monde après la Chine - dead giveaway huh!!!

    BTW - if you think of it as "sue" + "sheila" (some concatenation here...) it's easier!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 8, 2005 at 5:02 pm

    #564028

    I'll avoid to show my total ignorance in that subject and won't put any guess out there . But since you talk about Suchie (how the h3II do you write that??), I'll go with Japan (Just remembered I can edit those things ).

  • June 9, 2005 at 9:25 am

    #564267

    In my opinion, If you are returning the data to an application that you have control of the source code, the front end UI should be doing a check for null values and handle how to display the information. It's better seperation of the layers. The data layer handles data, the presentation layer handles display.

Viewing 15 posts - 1 through 15 (of 16 total)

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