Select date where <year part = '2014'>

Question

Select date where <year part = '2014'>

mattech06

SSCrazy

Points: 2484
More actions
July 3, 2014 at 5:12 am

#397000

Getting a bit frustrated over this simple select statement.
I'd like to select all entries (including full date) where the year part of the date column is say '2014'
SELECT
dbclaimcnt, dbclaimentered
FROM
EPSClaimsUOA
WHERE
dbclaimentered = '2014'
thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

there is a YEAR function that returns an integer, that can do what you are asking:

there is also a DATEPART function that can do the same, but you can pull out pieces like the integer representing a day, month week,year or other things if the need arose (quarters?):

SELECT

dbclaimcnt, dbclaimentered

FROM

EPSClaimsUOA

WHERE

YEAR(dbclaimentered) = 2014

SELECT

dbclaimcnt, dbclaimentered

FROM

EPSClaimsUOA

WHERE

select DATEPART(yy,dbclaimentered) = 2014

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mattech06 SSCrazy Points: 2484 More actions · Answer 2

thanks, they both do the job (minus the select in the second where sql statement)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Do note that using functions on the columns like that prevents any index seek for that predicate.

If that column is a date, then:

SELECT

dbclaimcnt, dbclaimentered

FROM

EPSClaimsUOA

WHERE

dbclaimentered >= '2014-01-01' and dbclaimentered < '2015-01-01'

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 4

Another often used solution to something like this is to have a calendar table with a schema similar to this:

CREATE TABLE dbo.calendar

(

theDate DATETIME,

theYear INT,

theQuarter TINYINT,

theMonth TINYINT,

weekOfYear TINYINT

theDayOfYear TINYINT

)

Then you do a query like this:

SELECT

dbclaimcnt,

dbclaimentered

FROM

EPSClaimsUOA

WHERE

EXISTS ( SELECT

1

FROM

dbo.calendar

WHERE

theYear = 2014 AND

dbclaimentered >= theDate AND

dbclaimentered <= theDate )

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

GilaMonster (7/3/2014)
Do note that using functions on the columns like that prevents any index seek for that predicate.
If that column is a date, then:
SELECT
dbclaimcnt, dbclaimentered
FROM
EPSClaimsUOA
WHERE
dbclaimentered >= '2014-01-01' and dbclaimentered < '2015-01-01'

Good example of why letting the OP select "Mark as Solution" is not a good idea.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 6

Michael Valentine Jones (7/3/2014)
GilaMonster (7/3/2014)
Do note that using functions on the columns like that prevents any index seek for that predicate.
If that column is a date, then:
SELECT
dbclaimcnt, dbclaimentered
FROM
EPSClaimsUOA
WHERE
dbclaimentered >= '2014-01-01' and dbclaimentered < '2015-01-01'
Good example of why letting the OP select "Mark as Solution" is not a good idea.

Technically that is a valid solution, just not necessarily the best performing solution. At least marking an answer as a solution doesn't close the thread.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Michael Valentine Jones (7/3/2014)
GilaMonster (7/3/2014)
Do note that using functions on the columns like that prevents any index seek for that predicate.
If that column is a date, then:
SELECT
dbclaimcnt, dbclaimentered
FROM
EPSClaimsUOA
WHERE
dbclaimentered >= '2014-01-01' and dbclaimentered < '2015-01-01'
Good example of why letting the OP select "Mark as Solution" is not a good idea.

It was marked as the solution long before I posted that, and the OP hasn't logged in since.

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

mattech06 SSCrazy Points: 2484 More actions · Answer 8

Thanks Gila and Jack point taken on index consideration.

'2014' was an example, that value's passed by the user.

Jones post - a good example of reading the posts fully before posting opinions perhaps.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

mattech06 (7/4/2014)
'2014' was an example, that value's passed by the user.

You can still use either Jack's or my solutions with a value passed from a user. You'd just need a local variable to use to build up a date

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

mattech06 SSCrazy Points: 2484 More actions · Answer 10

mattech06

SSCrazy

Points: 2484

July 4, 2014 at 2:43 am

#1727028

yep, thanks Gila