How to get the first day of preceding month

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » How to get the first day of preceding month

How to get the first day of preceding month

Rate Topic

Display Mode

Topic Options

Author

Message

manibad

Posted Tuesday, February 12, 2013 12:31 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:00 PM
Points: 24, Visits: 94

I need to get the first day of the followingmonth.How is it possible.I am unable to find a way to proceed

i.e my output should be like this
Scenario 1:
Input-2012/01/29
Output-2012/02/01

Scenario 2:
Input-2012/12/29
output-2013/01/01

I need to accumlate both the scenarios in same coding
Please guide me in solving this.

Thanks in advance

Post #1419149

Phil Parkin

Posted Tuesday, February 12, 2013 12:41 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

I can't really see what this has to do with SSIS, but here's one way.

declare @MyDate datetime

set @MyDate = '2012-12-29'

select DATEADD(dd, - (DAY(DATEADD(mm, 1, @MyDate )) - 1), DATEADD(mm, 1, @MyDate ))

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1419154

Sachin4all

Posted Tuesday, February 12, 2013 12:45 PM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:40 PM
Points: 67, Visits: 243

Here is an example query to do it

Declare @day date

set @day = '2012/01/29'

select @day

Select DAY(@day) as Noofdays

SELECT DATEADD(mm,1,DATEADD(dd,-(DAY(@day)-1),@day)) AS Day

Select DATEADD(yy,1,DATEADD(dd,-(DAY(@day)-1),@day)) as Day

Post #1419157

Lynn Pettis

Posted Tuesday, February 12, 2013 12:48 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:37 PM
Points: 21,627, Visits: 27,480

Using T-SQL:


declare @MyDate datetime;
set @MyDate = '20121229';
select dateadd(mm, datediff(mm,cast('19000101' as datetime),@MyDate) + 1, cast('19000101' as datetime)), @MyDate;

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1419158

manibad

Posted Tuesday, February 12, 2013 1:40 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:00 PM
Points: 24, Visits: 94

I have to work on SSIS to get the first day of following month and the output should be as string value.

Input:getdate()
Output:Firstday of the following month
format of output is=yyyymmdd

Sorry for not providing the perfect information in the previous post.

Post #1419173

« Prev Topic | Next Topic »

Permissions