Solved

Someone Help me Writing Date Script

Posted on 2009-04-09
5
172 Views
Last Modified: 2012-05-06
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,-DAY(GetDate()),GetDate()),101)
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
0
Comment
Question by:bsumanrao
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Try like this:



Set @StartDate = DATEADD(month, 0, DATEDIFF(day, 0, GETDATE()) - DATEPART(dy, GETDATE()) + 1)Set @EndDate = DATEADD(month, 0, DATEDIFF(day, 0, GETDATE()) - DAY(GETDATE()) + 1)
 

WHILE @StartDate < @EndDate

...

Open in new window

0
 
LVL 25

Accepted Solution

by:
reb73 earned 250 total points
Comment Utility
Try this -
Declare @StartDate datetime

Declare @EndDate datetime

Declare	@WorkDate datetime
 

Set @EndDate = CONVERT(varchar,DATEADD(d,-DAY(GetDate()),GetDate()),101)

Set @WorkDate =  '01/01/'+ CAST(YEAR(@EndDate)as Char(4))

--Print @StartDate

--Print @EndDate
 

--Create Table
 

While @WorkDate < @EndDate

Begin

	Set @StartDate = DATEADD(d, -1, DATEADD(m, 1, @WorkDate))
 

--Inserting Data into table
 

	set @WorkDate = DATEADD(m, 1, @WorkDate)

End

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Sorry just caught the January 31st start.  PRevious code gets January 1st.  It would help to know how you were using the date later to make sure my suggestion will work correctly.  I do this sort of analysis with dynamic dates where I am doing _date >= @start_date and _date < @end_date so it works using dates at midnight.
0
 

Author Closing Comment

by:bsumanrao
Comment Utility
Thanks  Reb........
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I don't think that the dateadd(m...) will work because adding a month to January 31 gives you Feb-28, but 1 month later is March 28.

Try this, which returns the start as day 1, and end as day 1 of next month.  If you depend upon enddate being the last day of the month, instead of the first day of next month then use the second script.

The difference would be:

SomeField between @Start and @End

vs

SomeField >= @start
and SomeField < @End
Declare 

      @StartDate    datetime

     ,@EndDate      datetime
 

set @startdate = ltrim(str((datepart(yy,getdate())*10000)+101))

set @enddate = dateadd(m,1,@startdate)

while @enddate < getdate()

begin

     select @startdate,@enddate
 

set @startdate = dateadd(m, 1,@startdate)

set @enddate = dateadd(m,1,@startdate)

end
 
 

go
 

Declare 

      @StartDate    datetime

     ,@EndDate      datetime
 

set @startdate = ltrim(str((datepart(yy,getdate())*10000)+101))

set @enddate = dateadd(m,1,@startdate)-1

while @enddate < getdate()

begin

     select @startdate,@enddate
 

set @startdate = dateadd(m, 1,@startdate)

set @enddate = dateadd(m,1,@startdate)-1

end

Open in new window

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now