December 8, 2004 at 5:32 pm
Hi
I am working with a sql server 2000 db table that has two columns (fromdate and todate) in datatime datatype. These fields are populated using a .net web application. Also the two fields are not required by user and whenever the user leave them blank I automatically assign '1/1/1800' as a value to avoid getting errors. And when ever the user retrieves values from those two columns I check to see if its '1/1/1800' and set the appropriate fields to blank. The above method work fine, but here's my question.
Is there a better way to do this? I tried assign a null value at first instead of putting '1/1/1800' value on my stored procedure whenever the user leave the fields blank but when I try to check for null on the data retrieval procedure it always fails here's the sql code on the procedure:
ALTER PROCEDURE
dbo.sptest
(
@STID uniqueidentifier,
@AppID uniqueidentifier
)
AS
declare
@FromDate datetime
set
@FromDate =
(SELECT FromDate
FROM StuTeach
WHERE StuTeachID = @STID AND
AppID = @AppID)
IF
(@FromDate = NULL)
SET @FromDate = '1/1/1900'
'fromdate'
print @FromDate
It seems like the @FromDate variable ignores the null check even though the colum is NULL on the db. How do I make @FromDate return NULL instead of not returning anything. Or why is the IF statement failing?
Any code help or suggestions would be greatly appreciated. Thanks in advance and I look forward to your response.
Dinesh
December 8, 2004 at 5:42 pm
1st, back in version 7 (I believe) the powers that be decided that you can't say x = null instead you have to say x IS NULL (stupid I think).
As for the other issue, you could just have your table allow the date fields to be null.
December 9, 2004 at 2:49 am
See SQL Server Central's most popular article http://qa.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp
Try IS NULL rather than = NULL unless you want to SET ANSI_NULLS OFF
December 9, 2004 at 4:17 am
you insert the null value in datetime data field , it insert the 1900-01-01 00:00:000 value it not insert the null in this field.
December 9, 2004 at 6:53 am
you insert the null value in datetime data field , it insert the 1900-01-01 00:00:000 value it not insert the null in this field.
This is not correct! However, SQL Server exhibits this behaviour:
use tempdb
create table #t
(
c1 datetime null
)
insert into #t values(null)
insert into #t values(0)
insert into #t values('')
select * from #t
drop table #t
c1
------------------------------------------------------
NULL
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
(3 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 9, 2004 at 8:55 am
First off thank you everyone for the responses. David, thanks for the link to the article. That was a great help. The procedure returned exactly the results I was looking for once I used IS NULL in the IF statement. And Frank yes I have notice that behavior in sql server as well when I tried to enter ' ' for a datetime value....I thought it was strange. Anyway, thank you for the help.
Dinesh
December 9, 2004 at 9:34 pm
You can avoid this...
IF (@FromDate = NULL)
SET @FromDate = '1/1/1900'
... by doing this... (just a bit faster, I think)
SET @FromDate = ISNULL(@FromDate,'1/1/1900')
Of course, if you defaulted the date fields to zero (as Frank's example implies), the date returned will be the 1st of January 1900 with no extra work at all.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply