none
SQL Query for Compare date

    Question

  • I've problem about sql server 2005.
    i want compare today with past date.
    let's say endDate and NewEndDate.

    I must compare today with  enddate if newenddate is null or ('')
    and i compare today with newEndDate if newEndDate heve date value.



    Wednesday, August 26, 2009 5:09 AM

Answers

  • CASE WHEN newenddate is null or newenddate = '' THEN <some value>
        ELSE <some other value> END

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 5:13 AM
  • This might do.

    select * from businessTrip
    WHERE getdate() > ISNULL(newenddate,enddate)
    


    Beyond Relational
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 7:30 AM
  • If you want to check the '' condition too, you can try the following

    select * from businessTrip
    WHERE getdate() > 
    	CASE 
    		WHEN newenddate IS NULL OR newenddate = '' THEN enddate
    		ELSE newenddate
    	END


    Beyond Relational
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 7:31 AM

All replies

  • CASE WHEN newenddate is null or newenddate = '' THEN <some value>
        ELSE <some other value> END

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 5:13 AM
  • I want put that code (my problem) in where clause.
    maybe like this

    select * from businessTrip
    where getdate() > enddate (if newenddate is null)  but where getdate()>newenddate

    what must i do?
    Wednesday, August 26, 2009 5:47 AM
  • This might do.

    select * from businessTrip
    WHERE getdate() > ISNULL(newenddate,enddate)
    


    Beyond Relational
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 7:30 AM
  • If you want to check the '' condition too, you can try the following

    select * from businessTrip
    WHERE getdate() > 
    	CASE 
    		WHEN newenddate IS NULL OR newenddate = '' THEN enddate
    		ELSE newenddate
    	END


    Beyond Relational
    • Marked as answer by Surbakti Wednesday, August 26, 2009 7:43 AM
    Wednesday, August 26, 2009 7:31 AM