Correct Date Range Syntax, I can't seem to get it right !!

[ajacode]

Using SQL2K I have an inbound Date Param @FiscalYear and
it is a char(4)

I want to use it in a Variable called Date Range

Previously the code was written for use in Dynamic SQL so
the whole things was written in a String context.

What I am having trouble getting the correct syntax for
the correct way to define the Date range for "Normal" SQL
and how to use it in the code.

Here's the Code:

declare @FiscalYear char(4)

declare @FiscalYear2 char(4)

select @FiscalYear2 = @FiscalYear - 1

declare @DateRange varchar(150)

THE OLD WAY To Declare a Date Range USING Dynamic SQL
select @DateRange = "BETWEEN CONVERT(DATETIME, '" +
@FiscalYear2 + "-10-01 12:00:00 AM', 102) AND CONVERT
(DATETIME, '" + @FiscalYear + "-09-30 11:59:59 PM', 102))"


Old Way in Body of Code

AND (StatusDate " + @DateRange + "


New Way NOT using Dynamic SQL ?

Define Date Range:

Use in standard Code ?

USE Variable Date Range in Standard Code??

AND (StatusDate <use Variable here>

I hope this makes sense. I have the basics but just not
the fine tuned syntax.

Thanks Beforehand

AJACODE

 

[clarkin]

You have an inbound paramater of type char(4) representing a year, such as '2003' or '1801'. You want to use this to limit something in a select?

How about get only records with a datetime column within that year?

--your param stuff done already: @FiscalYear could be '2001' or '1800'

-- convert your char(4) year to an int
DECLARE @intFiscalYear int
SELECT @intFiscalYear = CONVERT(int, @FiscalYear)

-- do a select comparing year of a datetime column to our int
SELECT * FROM myTable WHERE DATEPART(yyyy, myDatetimeColumn) = @intFiscalYear

:^) Short and sweet

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[ajacode]

clarkin,
Thanks for the Reply. My question was posed incorrectly.
I see not a new method, I seek a way to create proper syntax for the variable DateRange to handle a BETWEEN date range which itself handles 2 year (char) variables as inputs.

And then exactly what syntax to use this DateRange Variable in the main body of the SQL statement.

The old method used Dynamic SQL which treated the SQL statements as a big string and used quotes differently than if it were &quot;normal&quot; SQL code.

I hope this is a better way to ask the question

AJA

 

[SQLBill]

If I understand you correctly, this is what you are looking for:

CREATE PROCEDURE usp_dateproc
AS
DECLARE @1stdate DATETIME
, @2nddate DATETIME

SELECT col1, col2
FROM tablename
WHERE mydatefield BETWEEN @1stdate AND @2nddate

Then run:
EXEC usp_dateproc '2003-09-20 00:00:00', '2003-09-23 23:59:59'

-SQLBill

-SQLBill

 

[ajacode]

Thanks Bill but not exactly..

the inbound @FiscalYear comes in merely as
'2003' as CHAR (4)

the proc needs it concatenated with a date as I indicated above

I am merely looking for the proper syntax with commas and single and double quotes in the right place just as it is above but above is formatted using dynamic sql

sorry I wan't clearer on that

I do not have the option of an inbounc parameter like you formatted
EXEC usp_dateproc '2003-09-20 00:00:00', '2003-09-23 23:59:59'

 

[JamesLean]

What I would do is use the input year to build two &quot;proper&quot; date variables to use in a normal BETWEEN query. I'm assuming you want the fiscal year to start from the 1st Oct on the previous year to the 30th Sept of current year?

CREATE PROC myproc
  @yr char(4)
AS

DECLARE @start datetime,
  @end datetime

SET @start = CAST(CONVERT(int, @yr) - 1 AS char(4)) + '1001'
SET @end = @yr + '0930 23:59:59'

SELECT * FROM tbl
WHERE date_col BETWEEN @start AND @end

--James