Solved

Can someone Help me out writing Date code

Posted on 2009-04-09
6
240 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 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