Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
Medium Priority
?
272 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

721 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