Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
222 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 57
SQL Server 208R2 not recognizing DBF file in linked Server 11 51
Impove long SQL Stored Procedure Performance 14 69
Sql query 107 24
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

911 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

18 Experts available now in Live!

Get 1:1 Help Now