Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
229 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

809 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