Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 synatx Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 23, 2012 7:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM Points: 186, Visits: 473
 what is the syntax of range of year='2011-2012'
Post #1349094
 Posted Thursday, August 23, 2012 7:32 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 11:45 AM Points: 2,752, Visits: 5,190
 Assuming you're using the DATE datatype, any of these:WHERE YEAR(DateField) IN (2011, 2012)WHERE YEAR(DateField) BETWEEN 2011 AND 2012WHERE 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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 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) = 2012This 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 Group: General Forum Members Last Login: Today @ 5:43 PM Points: 23,516, Visits: 37,735
 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 2012WHERE 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'CheersThe 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.
Post #1349121
 Posted Thursday, August 23, 2012 10:08 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM Points: 186, Visits: 473
 how to pass this value '2011-2012' into accyear in procedure
Post #1349440
 Posted Friday, August 24, 2012 2:15 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 29, 2016 5:30 AM Points: 424, Visits: 1,271
 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)ASBEGIN SELECT * FROM MyTable WHERE DateField >= @StartDate AND DateField < @EndDate END`
Post #1349518
 Posted Monday, August 27, 2012 4:23 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, July 27, 2016 4:25 AM Points: 186, 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???
Post #1350301
 Posted Monday, August 27, 2012 5:04 AM
 SSC-Forever Group: General Forum Members Last Login: 2 days ago @ 5:18 AM Points: 45,619, Visits: 44,147
 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #1350316
 Posted Monday, August 27, 2012 8:35 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:43 PM Points: 23,516, Visits: 37,735
 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, ..., colnfrom dbo.SomeTablewhere SomeDateCol >= dateadd(yy, cast(left(@DumbDateParameter,4) as int) - 1900, 0) and SomeDateCol < dateadd(yy, cast(right(@DumbDateParameter,4) as int) - 1899, 0);`
Post #1350427

 Permissions