August 23, 2012 at 7:24 am
what is the syntax of range of year='2011-2012'
August 23, 2012 at 7:32 am
Assuming you're using the DATE datatype, any of these:
WHERE YEAR(DateField) IN (2011, 2012)
WHERE YEAR(DateField) BETWEEN 2011 AND 2012
WHERE DateField BETWEEN '20110101' AND '20121231'
If there's an index on the DateField, the last one will perform the best, as the use of the YEAR function precludes the use of the index.
If you're using DATETIME, the last one would become:
WHERE DateField >= '20110101' AND DateField < '20130101'
Cheers
August 23, 2012 at 7:36 am
Can you clarify your question?
Do you mean (for example) How do you select all DateTimes with the years 2011-2012?
If so, then it's:
WHERE YEAR(DateTime) = 2011 OR YEAR(DateTime) = 2012
This is OK for small amounts of data but will disable index usage so it's not so good for large data volumes.
August 23, 2012 at 8:01 am
Gazareth (8/23/2012)
Assuming you're using the DATE datatype, any of these:WHERE YEAR(DateField) IN (2011, 2012)
WHERE YEAR(DateField) BETWEEN 2011 AND 2012
WHERE DateField BETWEEN '20110101' AND '20121231'
If there's an index on the DateField, the last one will perform the best, as the use of the YEAR function precludes the use of the index.
If you're using DATETIME, the last one would become:
WHERE DateField >= '20110101' AND DateField < '20130101'
Cheers
The first two will kill the use of any index on DateField. The third will work, but even with the DATE data type I'd prefer the method you suggested for the DATETIME data type.
But have to agree with laurie-789651 as well, a better definition of what is requested would help.
August 23, 2012 at 10:08 pm
how to pass this value '2011-2012' into accyear in procedure
August 24, 2012 at 2:15 am
You would normally pass 2 date parameters to a procedure: @StartDate and @EndDate like this (using the syntax recommended above)
CREATE PROCEDURE dbo.xxx
(
@StartDate DateTime,
@EndDate DateTime
)
AS
BEGIN
SELECT * FROM MyTable
WHERE DateField >= @StartDate AND DateField < @EndDate
END
August 27, 2012 at 4:23 am
friends year is'2011-2012' data type is"nvarchar"
while i am paasing inputs on procedure its shows error of
"error converting datetime from character string."
please give me any suggestion???
August 27, 2012 at 5:04 am
Pass two parameters, one the start year, one the end year, both of type datetime (or date, or datetime2). One parameter of a mess of strings is just going to cause headaches (what happens when someone passes "2000-yyyy" by accident?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2012 at 8:35 am
raghuldrag (8/27/2012)
friends year is'2011-2012' data type is"nvarchar"while i am paasing inputs on procedure its shows error of
"error converting datetime from character string."
please give me any suggestion???
declare @DumbDateParameter nvarchar(9) = '2011-2012';
select
col1,
col2,
...,
coln
from
dbo.SomeTable
where
SomeDateCol >= dateadd(yy, cast(left(@DumbDateParameter,4) as int) - 1900, 0) and
SomeDateCol < dateadd(yy, cast(right(@DumbDateParameter,4) as int) - 1899, 0);
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply