How to do a SQL NOT NULL with a DateTime

Question

This might be simple  but I cant figure out how to do a simple DateTime with a NOT NULL   I want to do something like this   SELECT   FROM someTable WHERE thisDateTime IS NOT NULL   But that obviously doesn t work   I am using MS SQL 2005 if that matters  Thank you

User · Answer

Just to rule out a possibility - it doesn't appear to have anything to do with the ANSI_NULLS option, because that controls comparing to NULL with the = and <> operators. IS [NOT] NULL works whether ANSI_NULLS is ON or OFF.

I've also tried this against SQL Server 2005 with isql, because ANSI_NULLS defaults to OFF when using DB-Library.

User · Answer

erm it does work  I ve just tested it           Object   Table  dbo   DateTest     Script Date  09 26 2008 10 44 21         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE TABLE  dbo   DateTest        Date1   datetime  NULL       Date2   datetime  NOT NULL   ON  PRIMARY   GO Insert into DateTest  Date1 Date2  VALUES  NULL  1-Jan-2008   Insert into DateTest  Date1 Date2  VALUES   1-Jan-2008   1-Jan-2008   Go SELECT   FROM DateTest WHERE Date1 is not NULL GO SELECT   FROM DateTest WHERE Date2 is not NULL

User · Answer

SELECT   FROM Table where codtable not in  Select codtable from Table where fecha is null

User · Answer

I faced this problem where the following query doesn t work as expected   select 1 where getdate   lt  gt null   we expect it to show 1 because getdate   doesn t return null  I guess it has something to do with SQL failing to cast null as datetime and skipping the row  of course we know we should use IS or IS NOT keywords to compare a variable with null but when comparing two parameters it gets hard to handle the null situation  as a solution you can create your own compare function like the following   CREATE FUNCTION  dbo   fnCompareDates         DateTime1 datetime       DateTime2 datetime   RETURNS bit AS BEGIN     if   DateTime1 is null and  DateTime2 is null  return 1      if   DateTime1    DateTime2  return 1      return 0 END   and re writing the query like   select 1 where dbo fnCompareDates getdate   null  0

[sql-server] How to do a SQL NOT NULL with a DateTime?

Examples related to sql-server