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
 General SQL Server Forums
 New to SQL Server Programming
 Checking DateTime field in Procedure

Author  Topic 

jp2code
Posting Yak Master

175 Posts

Posted - 2008-07-24 : 11:04:53
I have a procedure I am trying to create, but I am unable to get the DateTime field to behave the way I want.

I'd like to be able to call my procedure and either include start date and end date or just start date and nothing or start date and a space:

EXECUTE sp_myProcedure '01/17/2005', '05/01/2006'
or
EXECUTE sp_myProcedure '01/17/2005'
or
EXECUTE sp_myProcedure '01/17/2005', ' '
What I have written does not work because SQL Server 2000 will not let me compare a DateTime to ' '.

What I would like to do is ask if the end date is valid, but I do not know how to post that question.

Could someone tell me how to ask SQL Server 2000 if my end date is a valid DateTime field?

CREATE PROCEDURE sp_myProcedure(@DateStart DateTime, @DateEnd DateTime) AS
SELECT [Operator], [Serial_Number], [Date_Time], [System]
FROM Company_Parts
WHERE
CASE WHEN (@DateEnd IS '') THEN (@DateStart <= Date_Time)
ELSE (Date_Time BETWEEN @DateStart AND @DateEnd)
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 11:10:41
CREATE PROCEDURE sp_myProcedure(@DateStart DateTime, @DateEnd DateTime) AS
SELECT [Operator], [Serial_Number], [Date_Time], [System]
FROM Company_Parts
WHERE Date_Time BETWEEN @DateStart AND (COALESCE(@DateEnd,date_time))

Instead of '' pass NULL to @DateEnd parameter

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jp2code
Posting Yak Master

175 Posts

Posted - 2008-07-24 : 11:18:50
COALESCE

Nice! Thanks for that. That is a new one for me.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 11:20:12
quote:
Originally posted by jp2code

COALESCE

Nice! Thanks for that. That is a new one for me.


Do google search for COALESCE to know more about it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -