Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
212 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
Comment Utility
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
Comment Utility
Hey, Thanks for ur Help........
0
 

Author Comment

by:bsumanrao
Comment Utility
That code gives me values only for jan for 3 times
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

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

Expert Comment

by:expertsoul
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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