Query Problem - 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 2005 Forums

Transact-SQL (2005)

Query Problem

Author

Topic

anujshah02
Starting Member

7 Posts

Posted - 2010-06-09 : 09:05:36

Hi All.. I am new to SQL programming and need some help in the below code:

DECLARE @1_metricDate varchar(20), @1_endDate varchar(20)
SET @1_metricDate = '4/1/2010';
SET @1_endDate = @1_metricDate;

SELECT
CASE
WHEN MONTH(@1_metricDate) IN (1,3,5,7,8,10,12)
THEN @1_endDate = REPLACE(@1_metricDate, DAY(@1_metricDate), 31)
WHEN MONTH(@1_metricDate) IN (4,6,9,11)
THEN @1_endDate = REPLACE(@1_metricDate, DAY(@1_metricDate), 30)
WHEN MONTH(@1_metricDate)= 2 AND (YEAR(@1_metricDate)%4 = 0)
THEN @1_endDate = REPLACE(@1_metricDate, DAY(@1_metricDate), 29)
ELSE @1_endDate = REPLACE(@1_metricDate, DAY(@1_metricDate), 28)
END

SELECT @1_endDate as EndDate;

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-09 : 09:17:48

You can't assign values to the variable in CASE Statements...

you must Use IF Statements rather then case.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-06-09 : 09:18:23

[code]
DECLARE @1_metricDate varchar(20), @1_endDate varchar(20)
SET @1_metricDate = '4/1/2010';
SET @1_endDate = @1_metricDate;

SELECT @1_endDate=
CASE
WHEN MONTH(@1_metricDate) IN (1,3,5,7,8,10,12)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 31)
WHEN MONTH(@1_metricDate) IN (4,6,9,11)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 30)
WHEN MONTH(@1_metricDate)= 2 AND (YEAR(@1_metricDate)%4 = 0)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 29)
ELSE REPLACE(@1_metricDate, DAY(@1_metricDate), 28)
END

SELECT @1_endDate as EndDate;
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-09 : 09:18:46

It will work for you

DECLARE @1_metricDate varchar(20), @1_endDate varchar(20)
SET @1_metricDate = '4/1/2010'
SET @1_endDate = @1_metricDate

SELECT
CASE
WHEN MONTH(@1_metricDate) IN (1,3,5,7,8,10,12)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 31)
WHEN MONTH(@1_metricDate) IN (4,6,9,11)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 30)
WHEN MONTH(@1_metricDate)= 2 AND (YEAR(@1_metricDate)%4 = 0)
THEN REPLACE(@1_metricDate, DAY(@1_metricDate), 29)
ELSE REPLACE(@1_metricDate, DAY(@1_metricDate), 28)
END as EndDate

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

anujshah02
Starting Member

7 Posts

Posted - 2010-06-09 : 09:31:00

thank you guys..

Its not working as I want but I think i can modify that..

Appreciate the help

anujshah02
Starting Member

7 Posts

Posted - 2010-06-09 : 09:38:55

I modified a bit.. to get the desired functionality but doesn't work

DECLARE @1_metricDate varchar(20), @1_endDate varchar(20)
SET @1_metricDate = '4/1/2010';
SET @1_endDate = @1_metricDate;

SELECT @1_endDate=
CASE
WHEN MONTH(@1_metricDate) IN (1,3,5,7,8,10,12)
THEN REPLACE(@1_metricDate, '/1/', '/31/');
WHEN MONTH(@1_metricDate) IN (4,6,9,11)
THEN REPLACE(@1_metricDate, '/1/', '/30/');
WHEN MONTH(@1_metricDate)= 2 AND (YEAR(@1_metricDate)%4 = 0)
THEN REPLACE(@1_metricDate, '/1/', '/29/');
ELSE REPLACE(@1_metricDate, '/1/', '/28/');
END

SELECT @1_endDate as EndDate;

Sachin.Nand

2937 Posts

Posted - 2010-06-09 : 09:48:41

What does not work?Is the result wrong?If yes then pls tell us what are you trying to acheive?
Why have you added those semicolons in the case statement?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

anujshah02
Starting Member

7 Posts

Posted - 2010-06-09 : 09:55:43

It's working now.. modified it.. and ya those semicolons are not needed.. thank you

Sachin.Nand

2937 Posts

Posted - 2010-06-09 : 10:20:50

You are welcome.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources