SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

SELECT	DATEADD(MONTH, 12 * DATEPART(YEAR, GETDATE()) + m.num -22800, 0)
FROM	(
		SELECT	 2 UNION ALL
		SELECT	 1 UNION ALL
		SELECT	 0 UNION ALL
		SELECT	-1 UNION ALL
		SELECT	-2 UNION ALL
		SELECT	-3 UNION ALL
		SELECT	-4 UNION ALL
		SELECT	-5 UNION ALL
		SELECT	-6 UNION ALL
		SELECT	-7 UNION ALL
		SELECT	-8 UNION ALL
		SELECT	-9
	) AS m(num)

quote:

---

Originally posted by jcelko

>> I inherited the query this section of code is in.<<

The fifth labor of Hercules was to clean the stables of King Augeas in a single day. The Augean stables held thousands of animals and were over a mile long. This story has a happy ending for three reasons: (1) Hercules solved the problem in a clever way (2) Hercules got one tenth of the cattle for his work (3) At the end of the story of the Labors of Hercules, he got to kill the bastard that gave him this job.

>> It works fine, however, rather than declaring @today ; SET @today = getdate().I want to set that date for 03/01/2011. <<

Yup, you are in the Stables. Why have a local variable for the current date? I don't know, I'm still new at this.

Why use the proprietary SMALLDATETIME; when we have the ANSI/ISO DATE data type? Again, I don't know.

And please start using ONLY ISO-8601 dates. I will learn this.

You can can now write CAST(CURRENT_TIMESTAMP AS DATE) maroon]This worked, thanks!

Changing getdate() to 2011-03-01 returns the error: Msg 298, Level 16, State 1, Line 13
The conversion from DATETIME data type to DATE; data time resulted in a smalldate time overflow error.

What do I need to change to get this to work? I'm want to pull data based on 2011-03-01 not the current date.

DECLARE @today DATE;
SET @today = CAST(CURRENT_TIMESTAMP AS DATE);

The est of this code is, well, what you find in stables. SQL is a data language and this mess is procedural code for COBOL style strings. We would have a Calendar and mot mock-COBOL. [size=2] When I get more proficient at SQL (and I have some time) I plan to revise the inherited queries. I appreciate your help![/maroon]

CREATE TABLE Fiscal_Calendar

(fiscal_period_name CHAR(12) NOT NULL PRIMARY KEY
CHECK (fiscal_period_name LIKE 'FY[12][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'),
cal_start_date DATE NOT NULL,
cal_end_date DATE NOT NULL,
CHECK(cal_start_date < cal_end_date);

Load it with the name of each fiscal month and the calendar date range of that fiscal month. A full century is easy to do and small

INSERT INTO Fiscal_Calendar
VALUES ('FY2011-01-00', '2011-03-01', '2012-02-28'),
etc

The query skeleton is now:

SELECT FC.fiscal_period_name, ..
FROM Fiscal_Calendar AD FC, etc
WHERE @my_date BETWEEN FC.cal_start_date AND FC.cal_end_date
AND ..;

This is 1-2 orders of magnitude faster than your old code and MUCH easier to maintain.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

---