SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Checking DateTime field in Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jp2code
Posting Yak Master

USA
175 Posts

Posted - 07/24/2008 :  11:04:53  Show Profile  Visit jp2code's Homepage  Reply with Quote
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

India
22744 Posts

Posted - 07/24/2008 :  11:10:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

USA
175 Posts

Posted - 07/24/2008 :  11:18:50  Show Profile  Visit jp2code's Homepage  Reply with Quote
COALESCE

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

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 07/24/2008 :  11:20:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000