Please help to find the first week and last week of the month

[rathna01]

Hi all

If any one has the Idea how to find the first week and last week of the month in SQL Server

Thanks in Advance
Chethan

 

[zhavic]

DECLARE @tDate datetime
DECLARE @nFirstWeek integer,
@nLastWeek integer

-- your date
SET @tDate = GETDATE()
-- or you can create this date by SET @tDate = CONVERT( datetime, STR( YOUR_YEAR, 4 ) + CASE WHEN YOUR_MONTH < 10 THEN '0' + STR( YOUR_MONTH, 1 ) ELSE STR( YOUR_MONTH, 2 ) END + '01', 112 )


SET @nFirstWeek = DATEPART( week, CONVERT( datetime, STR( YEAR( @tDate ), 4 ) + CASE WHEN MONTH( @tDate ) < 10 THEN '0' + STR( MONTH( @tDate ), 1 ) ELSE STR( MONTH( @tDate ), 2 ) END + '01', 112 ) )

IF MONTH( @tDate ) < 11
SET @nLastWeek = DATEPART( week, DATEADD( day, -1, CONVERT( datetime, STR( YEAR( @tDate ), 4 ) + CASE WHEN MONTH( @tDate ) < 9 THEN '0' + STR( MONTH( @tDate ) + 1, 1 ) ELSE STR( MONTH( @tDate ) + 1, 2 ) END + '01', 112 ) ) )
ELSE
SET @nLastWeek = DATEPART( week, DATEADD( day, -1, CONVERT( datetime, STR( YEAR( @tDate ) + 1, 4 ) + '0101', 112 ) ) )


SELECT @nFirstWeek AS FirstWeek, @nLastWeek AS LastWeek

-----------------------------------------------------------------------------
or in one SELECT statement

DECLARE @tDate datetime
-- your date
SET @tDate = GETDATE()

SELECT DATEPART( week, CONVERT( datetime, STR( YEAR( @tDate ), 4 ) + CASE WHEN MONTH( @tDate ) < 10 THEN '0' + STR( MONTH( @tDate ), 1 ) ELSE STR( MONTH( @tDate ), 2 ) END + '01', 112 ) ) AS FirstWeek,
CASE WHEN MONTH( @tDate ) < 11 THEN
DATEPART( week, DATEADD( day, -1, CONVERT( datetime, STR( YEAR( @tDate ), 4 ) + CASE WHEN MONTH( @tDate ) < 9 THEN '0' + STR( MONTH( @tDate ) + 1, 1 ) ELSE STR( MONTH( @tDate ) + 1, 2 ) END + '01', 112 ) ) )
ELSE
DATEPART( week, DATEADD( day, -1, CONVERT( datetime, STR( YEAR( @tDate ) + 1, 4 ) + '0101', 112 ) ) )
END AS LastWeek


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ClaireHCM]

I dont really understand what does that mean by first week and last week of a month.Could any body explain little bit?

 

[zhavic]

This is, as I understand it:

One year has 53 weeks ( I thing )
Last and the first week of the month means order of week in year.
For example them month January, 2003 has first week = 1 and last week = 5

or Jun, 2003 has first week = 23 and last week = 27

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[ClaireHCM]

oh...Thanks,I never thought like this!

 

[SQLBill]

Chethan needs to explain the request better and preferably provide examples. Requesting the first and last week of a month to ME means:

First week of June 2003 is 1 to 7 June
Last week of June 2003 is 29 and 30 June
(last FULL week of June is 22 to 28 June)

and I think that's what Chethan is looking for; the first FULL week and the last FULL week of a given month.

But I could be wrong...how about it Chethan? What ARE you looking for?

-SQLBill

 

[zhavic]

SQLBill,

I can use that 'ClaireHsu' says 'oh... I never thought like this!'

You may be right, now we need the answer from 'Chethan'

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[rathna01]

Hi All,

I am trying to solve the Daylight saving(+1 hour or -1 hour)problem for cisco phones. since the data is stored in unix time (ie in numbers)in SQL Server7(it doesnot support getutcdate() function), I solved the conversion from number to date, but I need to add 1 hour or substract 1 hour from the date. since daylight starts on every year on first week of sunday on April and ends with last week of sunday on october. Anyway I solved this myself by writing a functions to calculate the start date of daylight and end date of daylight.

Thank's all of you for your help ->

Chethan