Query to get late week in the month

Question

Post reply

Query to get late week in the month

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 13, 2007 at 7:28 am

#173452

Hi,
I have a query that has the following results:
Rundate
20070312
20070318
20070325
20070401
20070405
20070408
20070415
20070423
20070429
20070506
20070513
20070520
I want to retrieve the last day of month eg. 20070325. At the moment in my query it bring backs 3 weeks in the month and only looking for the last week. I can't hard code this.
CAn anyone help me with this?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Check if this helps you

http://blog.sqlauthority.com/2007/05/20/sql-server-scriptfunction-to-find-last-day-of-month/

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

steve smith-401573 SSChampion Points: 10889 More actions · Answer 2

Something to consider: The last week of the month is ALWAYS right before the first day of the following month. So, given a date, you can create a variable containing the first of the following month and then constrain the selected dates to be within 7 or 8 days of that date. It's convoluted, but it may work for you. Possibly build the whole condition within a CASE statement?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

I hope this will help you

-- create sample data

declare @sample table (rundate datetime)

insert @sample

select '20070312' union all

select '20070318' union all

select '20070325' union all

select '20070401' union all

select '20070405' union all

select '20070408' union all

select '20070415' union all

select '20070423' union all

select '20070429' union all

select '20070506' union all

select '20070513' union all

select '20070520'

-- Do the expected work

SELECT MAX(rundate) AS LastRunDateForEachCalendarMonth

FROM @Sample

GROUP BY DATEDIFF(MONTH, 0, RunDate)

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Also, WEEK is the most vague time period/interval available in SQL Server.

What is a week? Do you mean ISO week calculation? Ledger week? Week in month?

Always when dealing with week, please define what week is for you!

N 56°04'39.16"
E 12°55'05.25"

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

If your condition for the last week of the month is as simple as finding the first day of the week containing the last day of the month, the code below should do it. Just pick the column for the day of week that the week starts with.

You can also use the function on this link to find the first day of the week.

Start of Week Function:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

select
 EOM,
 First_Day_of_Week_Starting_Sun = dateadd(dd,(datediff(dd,-53684,a.EOM)/7)*7,-53684),
 First_Day_of_Week_Starting_Mon = dateadd(dd,(datediff(dd,-53690,a.EOM)/7)*7,-53690),
 First_Day_of_Week_Starting_Tue = dateadd(dd,(datediff(dd,-53689,a.EOM)/7)*7,-53689),
 First_Day_of_Week_Starting_Wed = dateadd(dd,(datediff(dd,-53688,a.EOM)/7)*7,-53688),
 First_Day_of_Week_Starting_Thu = dateadd(dd,(datediff(dd,-53687,a.EOM)/7)*7,-53687),
 First_Day_of_Week_Starting_Fri = dateadd(dd,(datediff(dd,-53686,a.EOM)/7)*7,-53686),
 First_Day_of_Week_Starting_Sat = dateadd(dd,(datediff(dd,-53685,a.EOM)/7)*7,-53685)
from
 (

  -- Last day of current month
  selectEOM = dateadd(mm,datediff(mm,-1,getdate()),-1)
 )  a

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

I just love people who take the effort to post feedback to their problems and the suggestions they get!

N 56°04'39.16"
E 12°55'05.25"

Chris Campbell-415954 SSCrazy Points: 2248 More actions · Answer 7

Try this on for size...

select

dateadd(d, -1, dateadd(m, 1, dateadd(d, (day(getdate()) * -1) + 1, getdate())))

The innermost dateadd functinon gets me to the first of the current month (assuming I want to use the current date):

dateadd(d, (day(getdate()) * -1) + 1) -- Today's date minus the day number of the month plus 1 day gets me to the first of the month.

The next functinon adds one month to it which gets me to the first of NEXT month.

The outermost function subtracts one day from the first of next month. This will always yield the last day of the current month.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

I posted a much simpler way to get the last day of the current month in my prior post. It also has the advantage of removing the time part of the datetime and working with all possible datetime values.

-- Last day of current month
select EOM = dateadd(mm,datediff(mm,-1,getdate()),-1)

Manoj-407065 Newbie Points: 1 More actions · Answer 9

Select convert( datetime,replace(str(year(dateadd("m", 1, Getdate())), 4), ' ', '0') + replace(str(month(dateadd("m", 1, Getdate())), 2), ' ', '0') + '01',112)-1 as LastDayThisMonth can also work for the same.

Cheers,

Manoj