null datetime in union query

• Lee Zard

  SSC Veteran

  Points: 293

  More actions

  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...  

         

• Adrienne250

  Right there with Babe

  Points: 737

  More actions

  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 ...

• Ken McKelvey

  SSCoach

  Points: 18896

  More actions

  February 20, 2007 at 11:11 am

  #690708

  SELECT CAST(NULL as datetime)

• Lee Zard

  SSC Veteran

  Points: 293

  More actions

  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!

• Dave W.

  SSC Enthusiast

  Points: 182

  More actions

  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.