bsumanrao
asked on
Can someone Help me out writing Date code
I want to have startdate & enddate for 2009
Startdate should be Jan 31st 2009
EndDate should be March 31st 2009(Since Last Accounting Month is March)
Since, i need to use these dates in While Loop to run Temp Table.
Since for Every month end I need to run Procedure. So, i need to insert the code for StartDate & EndDate
I already wrote Below Code, but it doesnt work for March...The code is taking March 28th instead of March 31st, Since, Feb Month runs for 28th.
Declare @StartDate datetime
Declare @EndDate datetime
Set @EndDate = CONVERT(varchar,DATEADD(d,
Set @StartDate = '1/31/'+ CAST(YEAR(@EndDate)as Char(4))
--Print @StartDate
--Print @EndDate
--Create Table
While @StartDate <= @EndDate
Begin
--Inserting Data into table
set @StartDate = dateadd(m, 1, @StartDate)
End
i am not good at writing Dates.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That code gives me values only for jan for 3 times
ASKER
I want to Get Jan, Feb & March Values....Can u Please check While Loop & Fix it out.........
Could you post the query you are executing? The query posted above won't give you same value.
I have made a small modification in the query though. It will work fine as posted, but it will give error if start date is Feb 28th. (as will the queries in your other posts).
Here's a query, it should work fine for all the scenarios. You can try this out and let me know if any questions
I have made a small modification in the query though. It will work fine as posted, but it will give error if start date is Feb 28th. (as will the queries in your other posts).
Here's a query, it should work fine for all the scenarios. You can try this out and let me know if any questions
Declare @StartDate datetime
Declare @EndDate datetime
Set @EndDate = CONVERT(varchar,DATEADD(d,-DAY(GetDate()),GetDate()),101)
Set @StartDate = '1/31/'+ CAST(YEAR(@EndDate)as Char(4))
Declare @i INT
Set @i = 1
While @i <= datediff(mm, @StartDate, @EndDate) + 1
Begin
print DATEADD(Month, DATEDIFF(Month,0,@startdate)+@i, -1)
Set @i = @i + 1
End
This query will get you StartDate and EndDate both for given months. Hope this helps.
Declare @StartDate datetime
Declare @EndDate datetime
Set @EndDate = CONVERT(varchar,DATEADD(d,-DAY(GetDate()),GetDate()),101)
Set @StartDate = '02/28/'+ CAST(YEAR(@EndDate)as Char(4))
Declare @i INT
Set @i = 1
While @i <= datediff(mm, @StartDate, @EndDate) + 1
Begin
Print 'Start Date'
print DATEADD(Month, DATEDIFF(Month,0,@startdate)+@i - 1, 0)
Print 'End Date'
print DATEADD(Month, DATEDIFF(Month,0,@startdate)+@i, -1)
Set @i = @i + 1
End
ASKER