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!
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hey whc4,
try this
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;
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"
"I need the data in the DocDate field to be output as a string"
Hey whc4,
try this for a string output;
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;
ASKER
Thanks! Exactly what I needed.
Cast(year(req.DocDate) as varchar) as DocYear
Might be a little less messy
Might be a little less messy
>>Might be a little less messy<<
To put it mildly, at least it is better then CONVERT(VARCHAR(4),DATEPAR
To put it mildly, at least it is better then CONVERT(VARCHAR(4),DATEPAR
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_Re
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;