Date "Rounding" 2

[Nickela]

I had a query in Microsoft Access that used the following WHERE clause to determine the rows in which to sum up.

WHERE BillingDate BETWEEN #1/1/2003# and #1/31/2003#

I am looking to recreate the same functionality in a SQL Server 2000 Stored Procedure and would like to make the stored procedure automatically set the appropriate date range according to the current date.

I forsee using the GETDATE() function to get the current date and then I would like to have the stored procedure set the Start Date to the first of the month and the End Date to the last day of the month for the date returned by GETDATE().

Has anybody tried to do this before? I haven't been able to find any way to round dates using T-SQL. Any thoughts? Thanks for your help.

Nickela

 

[lespaul]

What if on Feb 1, you want to run the report for January? By making the date selection "automatic" you will never get to choose what month to run. Is it possible that this situation will occur with your program? If so, you may want to re-think.


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Nickela]

This particular stored procedure is used to populate a field on a report that gives the monthly progress towards a revenue goal, so at all times it the current month what is needed. Thanks for the input, though.

Nickela

 

[zhavic]

Try this : ( I am using German dateformat dd.mm.yyyy )

DECLARE @tStartDate datetime
DECLARE @tEndDate datetime
DECLARE @tCurrentDate datetime

SET @tCurrentDate = GETDATE()

SET @tStartDate = CONVERT( datetime, '01.' + STR( MONTH( @tCurrentDate ) ) + '.' + STR( YEAR( @tCurrentDate ) ), 104 )
IF MONTH( @tCurrentDate ) < 12
SET @tEndDate = DATEADD( day, -1, CONVERT( datetime, '01.' + STR( MONTH( @tCurrentDate ) + 1, 2 ) + '.' + STR( YEAR( @tCurrentDate ) ), 104 ) )
ELSE
SET @tEndDate = DATEADD( day, -1, CONVERT( datetime, '01.01.' + STR( YEAR( @tCurrentDate ) + 1 ), 104 ) )

SELECT @tStartDate, @tEndDate

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.

 

[Nickela]

Thanks Zhavic. This definitely gets the correct dates for the range. Where would I add in some logic to make the time for the end date 23:59:59?

Nickela

 

[zhavic]

At the end of line:
SET @tEndDate = DATEADD( day, -1, CONVERT( datetime, '01.01.' + STR( YEAR( @tCurrentDate ) + 1 ) + ' 23:59:59', 104 ) )

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.

 

[Nickela]

Thanks for your help.

Nickela