Date function help!

Question

Date function help!

DataGuy

SSCrazy

Points: 2029
More actions
January 7, 2010 at 4:32 pm

#136352

I need to set a date variable that will capture the 25th day of the previous month, and I'm having a hard time figuring out how to write it. This is what i have so far, and obviously it starts on the 1st day of the previous month. How can I make it start on the 25th? or any other specific date for that matter?
/* Set the first day of the previous month */
declare @start_dt datetime
set @start_dt = (select dateadd(mm,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)))

Viewing 4 posts - 1 through 3 (of 3 total)

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

john.arnott SSChampion Points: 11882 More actions · Answer 1

You've made a good start by seeing that the key to this is using DATEDIFF and DATEADD with the "mm" option. I just went through a similar process to find the last day of the previous month, so your problem became a simple variation. To show how the code works, I've included a series of SELECTS that build on each other, eventually getting to the one you originally aimed for.

Declare @Now datetime

Set @now = getdate()

--

-- This counts month boundaries ('mm') between

-- SQL Server zero base date of 1900/01/01 and today (@now)

Select MonthsSinceBaseDate = Datediff(mm,0, @now)

--

-- This adds that many months to the base date, giving us

-- the first day of this month.

Select StartThisMonth = DateAdd(mm,Datediff(mm,0, @now),0)

--

-- This similar technique has been posted numerous times as a way

-- to return today's date without any time added (midnight).

Select StartOfToday = DateAdd(dd,Datediff(dd,0, @now),0)

--

-- Since we want to find a date in LAST month, we'll decrement

-- the monthcount before doing the DateAdd

Select StartLastMonth = DateAdd(mm,Datediff(mm,0, @now)-1,0)

--

-- Finally, since the twenty-fifth of the month is 24 days

-- after the first, add 24

Select TwentyFifthLastMonth = DateAdd(mm,Datediff(mm,0, @now)-1,24)

--edit to add this comment to perhaps clarify what's going on in this code:

The 24 in the last SELECT isn't exactly added on. It's the day-count equivalent to 1/25/1901 in the zero-based format of a datetime (0=1/1/1901, 1=1/2/1901, 2=1/3/1901, etc). The DATEADD adds the month count to that date and returns the date (currently) 1298 months after 1/25/1901.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

Code-1029433 (1/7/2010)
I need to set a date variable that will capture the 25th day of the previous month, and I'm having a hard time figuring out how to write it. This is what i have so far, and obviously it starts on the 1st day of the previous month. How can I make it start on the 25th? or any other specific date for that matter?
/* Set the first day of the previous month */
declare @start_dt datetime
set @start_dt = (select dateadd(mm,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)))

Simple... add 24 days.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DataGuy SSCrazy Points: 2029 More actions · Answer 3

DataGuy

SSCrazy

Points: 2029

January 8, 2010 at 10:07 am

#1101031

Thanks for the help guys!