Output SQL Date-type value as a string (year only)

I have the below SQL code.  The DocDate field in the database is of type datetime.  I need the data in the DocDate field to be output as a string, and specificaly I want to strip away the time as well as the month and day...I only need the year.  I am using this with an asp.net application, where I am using the output of the database to build a filesystem path and one level of the path consists of folders that are named by year (e.g. 1998, 2003, etc.)

However, I'd still like the "ORDER BY req.DocDate" portion of the code to order the results by the acutal datetime informaiton contained in the database.

Using MS SQL Server 2008 & MS Visual Studio 2008.

Thanks in advance!
SELECT req.DocumentTitle, req.Author, req.Organization, req.Description, req.DocDate, req.FollowUp, resp.DocumentID, resp.Organization, resp.DocumentTitle, resp.FileName, resp.FileType
FROM eog_docdb.dbo.Document req
LEFT OUTER JOIN (eog_docdb.dbo.Document_Responder d_r
INNER JOIN eog_docdb.dbo.Document resp
ON resp.DocumentID = d_r.ResponseDocID)
ON req.DocumentID = d_r.DocumentID
WHERE req.CategoryName = 'Information'
AND req.CategorySub1Name = 'Information Bulletins'
AND req.CategorySub2Name IS NULL
ORDER BY req.DocDate;

Open in new window

whc4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
SELECT req.DocumentTitle, req.Author, req.Organization, req.Description, CONVERT(char(4), req.DocDate, 112) DocDate, req.FollowUp, resp.DocumentID, resp.Organization, resp.DocumentTitle, resp.FileName, resp.FileType
FROM eog_docdb.dbo.Document req
LEFT OUTER JOIN (eog_docdb.dbo.Document_Responder d_r
INNER JOIN eog_docdb.dbo.Document resp
ON resp.DocumentID = d_r.ResponseDocID)
ON req.DocumentID = d_r.DocumentID
WHERE req.CategoryName = 'Information'
AND req.CategorySub1Name = 'Information Bulletins'
AND req.CategorySub2Name IS NULL
ORDER BY req.DocDate;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
appariCommented:
use year function to get the year part from datetime.
try this

SELECT year(req.DocDate) Year_DocDate, req.DocumentTitle, req.Author, req.Organization, req.Description, req.DocDate, req.FollowUp, resp.DocumentID, resp.Organization, resp.DocumentTitle, resp.FileName, resp.FileType
FROM eog_docdb.dbo.Document req
LEFT OUTER JOIN (eog_docdb.dbo.Document_Responder d_r
INNER JOIN eog_docdb.dbo.Document resp
ON resp.DocumentID = d_r.ResponseDocID)
ON req.DocumentID = d_r.DocumentID
WHERE req.CategoryName = 'Information'
AND req.CategorySub1Name = 'Information Bulletins'
AND req.CategorySub2Name IS NULL
ORDER BY req.DocDate;
0
AruspexCommented:
Hey whc4,


try this
SELECT req.DocumentTitle, req.Author, req.Organization, req.Description, DATEPART(year,req.DocDate) as DocYear, req.FollowUp, resp.DocumentID, resp.Organization, resp.DocumentTitle, resp.FileName, resp.FileType
FROM eog_docdb.dbo.Document req
LEFT OUTER JOIN (eog_docdb.dbo.Document_Responder d_r
INNER JOIN eog_docdb.dbo.Document resp
ON resp.DocumentID = d_r.ResponseDocID)
ON req.DocumentID = d_r.DocumentID
WHERE req.CategoryName = 'Information'
AND req.CategorySub1Name = 'Information Bulletins'
AND req.CategorySub2Name IS NULL
ORDER BY req.DocDate;
 

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
Just a reminder to all that have posted, the requirements from the author:
"I need the data in the DocDate field to be output as a string"
0
AruspexCommented:
Hey whc4,

try this for a string output;
SELECT req.DocumentTitle, req.Author, req.Organization, req.Description, CONVERT(VARCHAR(4),DATEPART(year,req.DocDate)) as DocYear, req.FollowUp, resp.DocumentID, resp.Organization, resp.DocumentTitle, resp.FileName, resp.FileType
FROM eog_docdb.dbo.Document req
LEFT OUTER JOIN (eog_docdb.dbo.Document_Responder d_r
INNER JOIN eog_docdb.dbo.Document resp
ON resp.DocumentID = d_r.ResponseDocID)
ON req.DocumentID = d_r.DocumentID
WHERE req.CategoryName = 'Information'
AND req.CategorySub1Name = 'Information Bulletins'
AND req.CategorySub2Name IS NULL
ORDER BY req.DocDate;
 

Open in new window

0
whc4Author Commented:
Thanks!  Exactly what I needed.
0
matthewsslCommented:
Cast(year(req.DocDate) as varchar) as DocYear

Might be a little less messy
0
Anthony PerkinsCommented:
>>Might be a little less messy<<
To put it mildly, at least it is better then CONVERT(VARCHAR(4),DATEPART(year,req.DocDate))  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.