Post reply

null datetime in union query

  • February 20, 2007 at 9:20 am

    #194252

    I have created a view that does a union query to put data together.  My problem is with datetime data types.  Below is a snippet of the code.  In the first Select, G6_ACT_DD will never have a value.  Is there a way to return a null value instead of 01/01/1900 00:00:00? 

     SELECT G.REC_DATE, 

              '' G6_ACT_DD

             FROM

             GPROCESS_HISTORY G

    WHERE...

            

    union all

    SELECT G6.REC_DATE,

              G6.G6_ACT_DD

               FROM  

            G6ACTION G6

        LEFT OUTER JOIN.....

    WHERE...  

           

  • February 20, 2007 at 10:42 am

    #690706

    I think there may be a zero in the data.  You could do this:

    SELECT G6_ACT_DD

    = case when G6_ACT_DD = 0 then null else G6_ACT_DD end ...

  • February 20, 2007 at 11:11 am

    #690708

    SELECT CAST(NULL as datetime)

  • February 20, 2007 at 12:21 pm

    #690729

    Thanks to both of you.  The case statement is the best choice for me.  It turns out a null date is not appropriate in this situation and I am using the case statement to get another date field if the G6_ACT_DD is not available.

    Thanks again!

  • February 21, 2007 at 2:37 pm

    #691027

    In that case, take a look at ISNULL() and COALESCE() to be able to go through a list of columns and return the value from the first non-null column.

Viewing 5 posts - 1 through 4 (of 4 total)

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