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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Can I use datepart as parameter in stored proc?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

echovault
Starting Member

20 Posts

Posted - 01/31/2008 :  18:48:18  Show Profile  Visit echovault's Homepage  Click to see echovault's MSN Messenger address  Send echovault a Yahoo! Message  Reply with Quote
Is it possible to use the datepart parameter, the one used by
DATEADD(datepart, number, date), in a stored procedure?

What exactly is that guy anyway? Is it a keyword? Can we use it at all outside of functions?

For example I want a stored procedure that returns a subset of a table based on a date range. I'd like for the client application to be able to specify the interval (i.e. day, week, month) and the number, just like the above function. But I don't know how to specify the datatype and use that parameter. Is it even possible? Something similar to this:

CREATE PROCEDURE GetDataForRange(
    @myDatePart datepart
  , number int
) AS
SELECT * FROM Table1 T 
WHERE T.Date BETWEEN GETDATE() 
  AND DATEADD(@myDatePart, number, GETDATE())

I know I could pass in a varchar and then write dynamic sql. I know I could use a single parameter, like number of days, and then go up from there. I'm not really looking for a solution here. It's just that I've run into a few situations where I thought it would be useful if I had access to that ... whatever it is, so I'm interested to know more about it.

Anybody care to shed some light on this for me?

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 01/31/2008 :  19:03:57  Show Profile  Reply with Quote
Probably not exactly what you are looking for, but you get the results you want:
DECLARE @Yak TABLE (Date DATETIME)

INSERT @Yak
SELECT GETDATE()
UNION ALL SELECT GETDATE() + 1
UNION ALL SELECT GETDATE() + 3
UNION ALL SELECT GETDATE() + 12
UNION ALL SELECT GETDATE() + 47
UNION ALL SELECT GETDATE() + 93


DECLARE @DatePart VARCHAR(10)
DECLARE @Number INT

SET @DatePart = 'WEEK'
SET @Number = 10

SELECT *
FROM @Yak
WHERE Date BETWEEN GETDATE() AND 
	CASE 
		WHEN @DatePart = 'SECOND' THEN DATEADD(SECOND, @Number, GETDATE())
		WHEN @DatePart = 'MINUTE' THEN DATEADD(MINUTE, @Number, GETDATE())
		WHEN @DatePart = 'HOUR' THEN DATEADD(HOUR, @Number, GETDATE())
		WHEN @DatePart = 'DAY' THEN DATEADD(DAY, @Number, GETDATE())
		WHEN @DatePart = 'WEEK' THEN DATEADD(WEEK, @Number, GETDATE())
		WHEN @DatePart = 'MONTH' THEN DATEADD(MONTH, @Number, GETDATE())
		WHEN @DatePart = 'YEAR' THEN DATEADD(YEAR, @Number, GETDATE())
		ELSE DATEADD(DAY, @Number, GETDATE())
	END
Or you could create a function that you could use instead of the case statement, if you need the same logic in multiple places.

Edited by - Lamprey on 01/31/2008 19:05:04
Go to Top of Page

echovault
Starting Member

20 Posts

Posted - 02/05/2008 :  08:40:54  Show Profile  Visit echovault's Homepage  Click to see echovault's MSN Messenger address  Send echovault a Yahoo! Message  Reply with Quote
Thanks for the reply, Lamprey. I always appreciate the insight of an Aged Yak Warrior.

I understand that I can write a case statement on a varchar variable. What I'm really looking for is some discussion on this elusive 'datatype' and whether it can be captured by a developer in and of itself. If this is not the proper forum for this dicussion, please let me know.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/05/2008 :  09:39:07  Show Profile  Reply with Quote
quote:
Originally posted by echovault

Thanks for the reply, Lamprey. I always appreciate the insight of an Aged Yak Warrior.

I understand that I can write a case statement on a varchar variable. What I'm really looking for is some discussion on this elusive 'datatype' and whether it can be captured by a developer in and of itself. If this is not the proper forum for this dicussion, please let me know.



You cannot use a parameter for the datepart in date functions.





CODO ERGO SUM
Go to Top of Page

echovault
Starting Member

20 Posts

Posted - 02/05/2008 :  10:06:04  Show Profile  Visit echovault's Homepage  Click to see echovault's MSN Messenger address  Send echovault a Yahoo! Message  Reply with Quote
I think that answers the question. Not what I was hoping to hear, but that's the way it goes sometimes.

Thanks all!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/05/2008 :  10:11:12  Show Profile  Reply with Quote
you may use dynamic sql to achieve this
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.08 seconds. Powered By: Snitz Forums 2000