Date Parameter Help - SQL Server Forums

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)

Date Parameter Help

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-26 : 15:06:22

I have a query that I want the user to be able to put in the month as parameter and year only. For example if they type in March for parameter 1 and 2005 for parameter 2, it will retrieve anything end of that month from that year. For example it will be like March 2005 will be like 03/31/2005.

Example of what I have:

CREATE PROC SP_CUSTOMER
@DATE AS SMALLDATETIME
AS
SELECT ID, CUSTOMER, ADDRESS
FROM CUSTOMER
WHERE CUSTOMER.CREATE_DATE = @DATE

What I'm hoping to achieve, but my syntax is not right:

CREATE PROC SP_CUSTOMER
@MONTH AS SMALLDATETIME,
@YEAR AS SMALLDATETIME
AS
SELECT ID, CUSTOMER, ADDRESS
FROM CUSTOMER
WHERE CUSTOMER.CREATE_DATE = DATE(@MONTH/@YEAR,)

Once they picked let say March, 2005. It will be like 03/31/2005.

Any ideas?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 15:33:14

Does this help, probably a better way of writing this....

DECLARE @Month int, @Year int, @Date smalldatetime, @txtdate varchar(10)
SET @Month = 3
SET @Year = 2005
SET @txtDate = CONVERT(varchar(10),(@Year * 10000) + (@Month * 100) + 1)

SET @Date = CONVERT(smalldatetime,DATEADD(dd,-1,DATEADD(mm,1,@txtDate)))
SELECT @Date

Andy

Beauty is in the eyes of the beerholder

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-26 : 17:13:06

This will give you the rows where the CREATE_DATE is the last day of the month. This assumes that CREATE_DATE has no time component, CREATE_DATE = '2005/3/31 00:00:00', not '2005/3/31 14:35:37'

declare	@Month int
declare	@Year int
set	@Month = 3
set	@Year = 2005

select
	ID,
	CUSTOMER,
	ADDRESS
from
	CUSTOMER
where
	CUSTOMER.CREATE_DATE =
	-- Last day of month
	dateadd(dd,-1,dateadd(mm,@Month,dateadd(yy,@Year-1900,0)))

CODO ERGO SUM

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-26 : 17:21:49

I knew that........

Beauty is in the eyes of the beerholder

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-27 : 16:03:03

Awesome guys. Thanks.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-27 : 16:05:25

Just out of curiosity. If I want the months like 'March' to be like '03', do I just need to set March to have value 3? Or how would I do it so that if the user puts in March it will know it's '03'?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources