Re: Format Date in GroupBy query

On Thu, 14 Apr 2005 16:13:02 -0700, J wrote:

>What is the syntax for a query that will give me the Count of [PartID]
>shipped each month. I think I need to do a Count on PK_ID and Group By the
>DateShipped but how do I format the date. I tried using DateName but I can
>only get it to work if I split out the Year and month. Thanks for the help.
>The raw data looks like
>PK_ID PartID DateShipped
>1 KLP098A 2005-04-11 10:59:05
>2 PLL907C 2005-04-12 10:43:03
>3 LPK761F 2005-05-15 10:23:07
>
>And I want the output to be
>Month PartsShipped
>Mar-2005 2
>May-2005 1

Hi J,

With no DDL ans sample data to go on (see www.aspfaq.com/5006), this is
of course just guesswork - but you might want to try:

SELECT SUBSTRING(CONVERT(varchar(11), DateShipped, 106), 4, 8) AS
Month,
COUNT(*) AS PartsShipped
FROM YourTable
GROUP BY SUBSTRING(CONVERT(varchar(11), DateShipped, 106), 4, 8)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.