CODEWITHSUNDEEP
sqlsql-server
user565
user565

Reputation: 1001

How to select dates in MSSQL

I am trying to writing a query which can return a result if date time column(fromdate) is either null or fromdate is before a current date. Here is my query:

select * from patients where from date =null OR from date  <= cast(cast(getdate() as date)as datetime)

but this query returning empty result. Can someone confirm me both checking null OR before currentDate check in query is valid or not ?

---------Apply filter on more than 1 column:-----------

SELECT *
FROM patients
WHERE(fromDate = NULL
      OR t1.fromDate <= CONVERT(DATE, GETDATE()))
     AND (t1.toDate = NULL
          OR t1.toDate >= CONVERT(DATE, GETDATE()));

I have a null column in todate in table but apply like this give me empty result

Upvotes: 1

Views: 69

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37347

Try this:

select * from patients where [fromdate] is null OR [fromdate]  <= cast(getdate() as datetime)

Upvotes: 1

mzh
mzh

Reputation: 525

This will work for you 

SELECT *
FROM patients
WHERE fromdate IS NULL
      OR fromdate <= CONVERT(DATE, GETDATE());

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You would do:

select p.*
from patients p
where p.fromdate is null or p.fromdate <= cast(getdate() as date);

Notes:

  • According to your question fromdate does not have a space in it.
  • The correct comparison for NULL is IS NULL, not = NULL.
  • There is no need to cast a date back to a datetime for the comparison.

Upvotes: 1

Related Questions