Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
265 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



i am not good at writing Dates.
0
Comment
Question by:bsumanrao
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
expertsoul earned 500 total points
ID: 24109926
You can try this:
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 = 0
 
While @i <= datediff(mm, @StartDate, @EndDate)
Begin
 
print dateadd(mm, @i, @StartDate)
Set @i = @i + 1
 
End

Open in new window

0
 

Author Closing Comment

by:bsumanrao
ID: 31568637
Hey, Thanks for ur Help........
0
 

Author Comment

by:bsumanrao
ID: 24110313
That code gives me values only for jan for 3 times
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:bsumanrao
ID: 24110324
I want to Get Jan, Feb & March Values....Can u Please check While Loop & Fix it out.........
0
 
LVL 12

Expert Comment

by:expertsoul
ID: 24111113
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
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

Open in new window

0
 
LVL 12

Expert Comment

by:expertsoul
ID: 24111235
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

Open in new window

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

617 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