Trying to display 00/00/000 for a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to display 00/00/000 for a date

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-31 : 16:14:06

I have this function to check and see if a date is NULL, if it is then I want to show the date as 00/00/0000 (this is in Active Reports so I don't have access to formatting options, kinda).

However, since sql server rejects dates before January 1, 1753 my date of 00/00/0000 throws an error.

Is there a way to show my 00/00/0000? I don't need to store it, I just need it to show up as '00/00/0000' is this possible?

Here's the function I'm using now:

CREATE FUNCTION dbo.CheckNull_Date(@theDate DATETIME)
RETURNS DATETIME
AS
BEGIN

IF (@theDate IS NULL)
SET @theDate = '17530101'

return (@theDate)
END

--Nick

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-31 : 16:52:12

How about returning a string ?

Srinika

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-31 : 17:31:09

quote:
Originally posted by Srinika

How about returning a string ?

Srinika

I'm gonna need a little hand holding on this one.....

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-31 : 17:55:19

[code]CREATE FUNCTION dbo.CheckNull_Date(@theDate DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @newDate varchar(50)
IF (@theDate IS NULL)
SET @newDate = '00/00/0000'
ELSE
SET @newDate = CONVERT(varchar(50), @theDate, 101)

return (@newDate)
END[/code]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-31 : 17:59:52

quote:
Originally posted by Lumbago

CREATE FUNCTION dbo.CheckNull_Date(@theDate DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @newDate varchar(50)
IF (@theDate IS NULL)
SET @newDate = '00/00/0000'
ELSE
SET @newDate = CONVERT(varchar(50), @theDate, 101)

return (@newDate)
END

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Nice, thanks.
--Nick

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-31 : 18:26:42

simplify a bit

CREATE FUNCTION dbo.CheckNull_Date(@theDate DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @newDate varchar(50)

  SELECT @newDate = isnull(CONVERT(varchar(50), @theDate, 101), '00/00/0000')

return (@newDate)
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 01:06:24

Or,

in ActiveReports FetchData event, check for null there.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources