SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


synatx


synatx

Author
Message
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
what is the syntax of range of year='2011-2012'
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3969 Visits: 5798
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
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38891 Visits: 38508
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.

Cool
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)
raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
how to pass this value '2011-2012' into accyear in procedure
laurie-789651
laurie-789651
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1272
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



raghuldrag
raghuldrag
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 473
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???
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86150 Visits: 45229
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

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


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38891 Visits: 38508
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);




Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search