Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

synatx Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 7:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
what is the syntax of range of year='2011-2012'
Post #1349094
Posted Thursday, August 23, 2012 7:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 1,976, Visits: 3,280
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
Post #1349100
Posted Thursday, August 23, 2012 7:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1349103
Posted Thursday, August 23, 2012 8:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1349121
Posted Thursday, August 23, 2012 10:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
how to pass this value '2011-2012' into accyear in procedure
Post #1349440
Posted Friday, August 24, 2012 2:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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


Post #1349518
Posted Monday, August 27, 2012 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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???
Post #1350301
Posted Monday, August 27, 2012 5:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350316
Posted Monday, August 27, 2012 8:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 23,397, Visits: 32,239
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);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1350427
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse