Thread: Help in query..

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    29
    Rep Power
    0

    Help in query..


    I am fetching a result from a query which is giving me NULL, I want to show it as '-', my problem is that the colum is of datetime, please help me out, the query is as bellow which is in SQL Server. I had tried case and COALESCE also it give me the error

    Code:
    select user_id ,case when ACTIVATION_TSTAMP is null then '-' else tum.ACTIVATION_TSTAMP end ACTIVATION_TSTAMP,
     from TB_USERID_MASTER where user_id='001'
    error is : The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by gurukrupa_1989
    error is : The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
    what you can do is convert the datetime to a string to match the dash character datatype
    Code:
    SELECT user_id 
         , CASE WHEN activation_tstamp IS NULL 
                THEN '-' 
                ELSE CONVERT(VARCHAR,activation_tstamp,100)
            END activation_tstamp
      FROM tb_userid_master 
     WHERE user_id = '001'
    best practice, however, is to run the query like this --
    Code:
    SELECT user_id 
         , activation_tstamp
      FROM tb_userid_master 
     WHERE user_id = '001'
    and replace the NULL with '-' in your front end application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    29
    Rep Power
    0
    Thanks........

IMN logo majestic logo threadwatch logo seochat tools logo