Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to get the first day of preceding month Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 12:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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
Posted Tuesday, February 12, 2013 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,100, Visits: 11,899
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1419154
Posted Tuesday, February 12, 2013 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 3, 2014 1:27 PM
Points: 67, Visits: 267
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
Posted Tuesday, February 12, 2013 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
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
Posted Tuesday, February 12, 2013 1:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:46 PM
Points: 91, Visits: 143
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 »

Add to briefcase

Permissions Expand / Collapse