Case statement - 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 2008 Forums

Transact-SQL (2008)

Case statement

Author

Topic

ava88
Starting Member

2 Posts

Posted - 2009-03-17 : 10:59:45

declare @begin datetime
declare @end datetime
DECLARE @mydate DATETIME
declare @dd int
SELECT @mydate = getdate()
SELECT @dd = DATEPART(DAY, GETDATE())

select @begin= convert (varchar(25), datepart(mm, CONVERT(VARCHAR(25),DATEADD(dd,-(DatePart(dd, getdate())),@mydate),101)) )
+ '/ 01/'+ convert(varchar(25), datepart(yy, CONVERT(VARCHAR(25),DATEADD(dd,-(DatePart(dd, getdate())),@mydate),101)) ) ----it is working up to this part
select @end=CONVERT(VARCHAR(25),DATEADD(dd,-(DatePart(dd, getdate())),@mydate),101)

SELECT *
from table
where
Case When @dd = 1 Then
start_date Between @begin And @end
Else
(start_date >@end and start_date <= dateadd(day,-1,getdate()) )
End

-----
-------------here if the 'start date' is 1st of the month, then it should run for prior month else it should run for the current month till the prior day....

The ERROR is 'incorrect syntax near the keyword between'

let me know if you have any ideas...... THe CASE statement is not working right......

Thanks,

Ava

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-17 : 11:37:45

why don't you use
(@dd = 1 and start_date Between @begin And @end ) or (@dd <> 1 and (start_date >@end and start_date <= dateadd(day,-1,getdate()) )
instead of this strange case?

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

ava88
Starting Member

2 Posts

Posted - 2009-03-17 : 12:24:59

Thanks....

Even this logic works.....

SELECT customerid, firstname, lastname
FROM dbo.Customer
WHERE hiredate BETWEEN CASE
WHEN @startdate IS NOT NULL THEN @startdate
ELSE hiredate
END
AND CASE
WHEN @enddate IS NOT NULL THEN @startdate
ELSE hiredate
END

Thanks..........

Ava

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-18 : 04:18:38

yes, know you understand that CASE works with values , not with conditions ? :)

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 04:46:05

Why make things more difficult than necessary?
Your two conditions are really the same condition. See this rewrite and example code in next post.

DECLARE @Begin DATETIME,
	@End DATETIME

SELECT	@Begin = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE() - 1), 0),
	@End = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

SELECT	* 
FROM	Table1
WHERE	start_date >= @Begin
	AND start_date < @End

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 04:51:41

Example of algorithm

SELECT	theDate AS [Current Date],
	DATEADD(MONTH, DATEDIFF(MONTH, 0, theDate - 1), 0) AS [>= Begin],
	DATEADD(DAY, DATEDIFF(DAY, 0, theDate), 0) AS [< End]
FROM	(
		SELECT	CAST('20090227' AS DATETIME) AS theDate UNION ALL
		SELECT	'20090228' UNION ALL
		SELECT	'20090301' UNION ALL
		SELECT	'20090302' UNION ALL
		SELECT	'20090303' UNION ALL
		SELECT	'20090304' UNION ALL
		SELECT	'20090305'
	) AS d


Current Date  >= Begin    < End
------------  ----------  ----------
2009-02-27    2009-02-01  2009-02-27
2009-02-28    2009-02-01  2009-02-28
2009-03-01    2009-02-01  2009-03-01
2009-03-02    2009-03-01  2009-03-02
2009-03-03    2009-03-01  2009-03-03
2009-03-04    2009-03-01  2009-03-04
2009-03-05    2009-03-01  2009-03-05

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources