Menu
How does one handle a DateTime with a NOT NULL?
I want to do something like this:
SELECT * FROM someTable WHERE thisDateTime IS NOT NULL
But how?
944
Description. The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Use model. myDate. HasValue. It will return true if date is not null otherwise false.
DateTime is a Value Type like int, double etc. so there is no way to assigned a null value.
In SQL Server just insert NULL in the Datetime column: INSERT INTO Table(name, datetimeColumn, ...) VALUES('foo bar', NULL, ..);
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
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
