CODEWITHSUNDEEP
sqlsql-server
RalphBiggerton
RalphBiggerton

Reputation: 199

Selecting group of years from date field

I'm trying to get a list of years from a date field that's stored as an nvarchar so am thinking doing a subquery to convert the date then select the year is the best way to go but having a hard time setting it up.

select datepart(yyyy, 
(
SELECT convert(date,'21-02-12 6:10:00 PM',5) datenum
) 
) as [year]
from SalesReport_AllDBs
group by datepart(yyyy, [datenum])

Any advice would be helpful to get this set up correctly

Upvotes: 0

Views: 25

Answers (2)

KeithL
KeithL

Reputation: 5594

select distinct year(cast([datenum] as date)) year
from SalesReport_AllDBs

Upvotes: 0

JNevill
JNevill

Reputation: 50034

The subquery should go in your FROM clause:

SELECT datepart(yyyy, mydate) as datenum
FROM (SELECT convert(date, yourdatestringfield ,5) as myDate FROM SalesReport_AllDBs) as years
GROUP BY datepart(yyyy,mydate);

Or in one query without a subquery, which is a lot nicer looking:

SELECT datepart(convert(date, yourdatestringfield ,5)) as datenum
FROM SalesReport_AllDBs
GROUP BY datenum

You should really just fix the table to hold dates instead of strings though. This is just going to lead to some nightmare scenarios and a slow slow query.

Upvotes: 1

Related Questions