Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

GROUP by SMALLDATETIME field

Posted on 2005-02-28
2
Medium Priority
?
565 Views
Last Modified: 2010-05-18
I am needing to group a SMALLDATETIME field by YEAR and MONTH (no day needed).  The format of the DATE field below is MM/DD/YYYY.  Can someone provide the syntax for doing this via a select query?

Here is the table I need to do this on.

RECORD                      int      4      0
[ITEM NUMBER]      nvarchar      10      1
[INV NUMBER]      nvarchar      25      1
[DATE]                      smalldatetime      4      1   <----------this is the field I need to group on
[ACCT NUMBER]      nvarchar      50      1
[ITEM DESC]      nvarchar      255      1
QTY                      int      4      1
CITY                      nvarchar      50      1
STATE                      nvarchar      4      1
ZIP                      nvarchar      12      1

Let me know if any more info is needed.

Saladart
0
Comment
Question by:saladart
  • 2
2 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13424834
Select CONVERT(CHAR(6), [DATE], 112) AS YYYYMM
From Table1
GROUP BY CONVERT(CHAR(6), [DATE], 112) AS YYYYMM
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13424839
Let's try that again:
Select CONVERT(CHAR(6), [DATE], 112) AS YYYYMM
From Table1
GROUP BY CONVERT(CHAR(6), [DATE], 112)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question