Help with Datetime field ...Please help!

Question

Help with Datetime field ...Please help!

Dinesh Ranasinghe

SSC Journeyman

Points: 90
More actions
December 8, 2004 at 5:32 pm

#162859

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'
print
'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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

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.

David.Poole SSC Guru Points: 76138 More actions · Answer 2

See SQL Server Central's most popular article http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Try IS NULL rather than = NULL unless you want to SET ANSI_NULLS OFF

LinkedIn Profile

sadheesh kumar m Old Hand Points: 385 More actions · Answer 3

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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

(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]

Dinesh Ranasinghe SSC Journeyman Points: 90 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Help with Datetime field ...Please help!

Cookies on SQLServerCentral