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.
This question is related to
sql-server
SELECT * FROM Table where codtable not in (Select codtable from Table where fecha is null)
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.
I faced this problem where the following query doesn't work as expected:
select 1 where getdate()<>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
Source: Stackoverflow.com