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.
| Author |
Topic |
|
anujshah02
Starting Member
7 Posts |
 Posted - 2010-06-09 : 13:49:47
|
| I need help in the below code, as I am new to SQL programming:I am basically trying to get the end date of the specified month.Example: when I enter date as '4/1/2010' i need to find the end date of the same month.. i.e.. aprilI wrote the code below:Its working but the code replaces all '1' in the date...If input is '4/1/2010' it gives me putput as '4/30/20300'but i want it as '4/30/2010'DECLARE @1_metricDate varchar(20), @1_endDate varchar(20)SET @1_metricDate = '4/1/2010'SET @1_endDate = @1_metricDateSELECTCASEWHEN 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 |
|
|
anujshah02
Starting Member
7 Posts |
Posted - 2010-06-09 : 13:51:46
|
| Or anyone can help me to get another logic to get the same.. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-09 : 13:54:31
|
Just use select DATEADD(dd,-(DAY(DATEADD(mm,1,@date))),DATEADD(mm,1,@date)) and pass a datetime field to @date |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-09 : 13:57:03
|
SELECT DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @1_metricDate)+1, 0))
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-09 : 13:58:17
|
Oh!I was a BIT too late.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
anujshah02
Starting Member
7 Posts |
Posted - 2010-06-09 : 14:18:21
|
| thanks you all |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Help needed !
Tara Kizer