First Day of Next Month

Question

Post reply

First Day of Next Month

Fred1n

SSC-Addicted

Points: 487
More actions
February 11, 2004 at 5:26 pm

#152039

I'm trying to find the 1st day of next month and I was trying to enhance Michael Rosquist routines. He put two routines together, one that returns the last day of the previous month and another one that returns the first day of current month.

SELECT DATEADD(dd, CAST(DATEDIFF(DAY,'17530101',CURRENT_TIMESTAMP) AS int)
- DAY(CURRENT_TIMESTAMP), '17530101')
as LastDayPreviousMonth
SELECT DATEADD(dd, CAST(DATEDIFF(DAY,'17530101',CURRENT_TIMESTAMP) AS int)
- DAY(CURRENT_TIMESTAMP) + 1, '17530101')
as FirstDayCurrentMonth
Anyone have any suggestions on finding the first day of the next month??

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

What are you truing to find out? The Day as in Monday, Tuesday etc or the Number 1 to 31.

Kindest Regards,

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

SELECT CAST(LEFT(GETDATE()-DAY(GETDATE()),11) AS datetime) LastDayLastMo,

CAST(LEFT(GETDATE()-DAY(GETDATE())+1,11) AS datetime) FirstDayThisMo,

CAST(LEFT(DATEADD(m,1,GETDATE()-DAY(GETDATE())+1),11) AS datetime) FirstDayNextMo

--Jonathan

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I prefer this method.

SELECT

DATEADD(d,-1,DATEADD(m,datediff(m,0,getdate()),0)) as LastDayPreviousMonth,

DATEADD(m,datediff(m,0,getdate()),0) as FirstDayOfCurrentMonth,

DATEADD(m,1,DATEADD(m,datediff(m,0,getdate()),0)) as FirstDayOfNextMonth

Mohit Nayyar Ten Centuries Points: 1008 More actions · Answer 4

We can try this with time

select dateadd(dd, -day(getdate()), getdate()) as Last_Day_Previous_Month,

dateadd(dd, -day(getdate())+1, getdate()) as First_Day_Current_Month,

dateadd(mm, 1, dateadd(dd, -day(getdate())+1, getdate())) as First_Day_Next_Month

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 5

http://www.geocities.com/sqlserverexamples/date5.htm

Goood site too

Declare @Fdaynm datetime -- First Day of Next Month

-- Set the @Fday to the first day of the next month

-- By adding a month to the current date and then

-- subtracting the number of days from the calculated date next month and then add 1

-- This keeps the existing time

Set @Fdaynm = dateadd(day,-1*(day(getdate())-1),dateadd(month,1,getdate()))

-- Print the @Fdaynm

select @Fdaynm

-- Another Way although this sets the time to 00:00:00.000

set @Fdaynm = cast(

cast(datepart(year,dateadd(month,1,getdate())) as char(4)) + '-' +

cast(datepart(mm,dateadd(month,1,getdate())) as char(2)) + '-01'

as datetime)

select @Fdaynm

My Blog: http://dineshasanka.spaces.live.com/

DAVNovak Hall of Fame Points: 3120 More actions · Answer 6

Here are two sql statements. The first when will return the date as mm/dd/yyyy. The second will return the day of the week such as Monday. 1) SELECT CONVERT(varchar(2), MONTH(DATEADD(month, 1, GETDATE()))) + '/1/' + CONVERT(char(4), YEAR(DATEADD(month, 1, GETDATE()))) 2) SELECT DATENAME(weekday, CONVERT(varchar(2), MONTH(DATEADD(month, 1, GETDATE()))) + '/1/' + CONVERT(char(4), YEAR(DATEADD(month, 1, GETDATE()))))

Fred1n SSC-Addicted Points: 487 More actions · Answer 7

Fred1n

SSC-Addicted

Points: 487

February 12, 2004 at 4:24 pm

#493905

Thanks for all the help...