ASP page with SQL query help needed...

itbizman · #1 (**permalink**) 07-20-04, 09:57

Hello,

I have the following SQL query I'm using to pull data from an access database:

SELECT Month([TransEntryDate]) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY Month([TransEntryDate]);

This query groups data by month and gives me a sum for each month. The problem is that my data returned for the months are represented numerically (1 for Jan, 2 for Feb, 3, for March, etc.) How can I edit the above to display the month name Jan instead of 1, etc? Your help is appreciated!

ASP-Hosting.ca · #2 (**permalink**) 07-20-04, 18:14

You can map the month numbers to their respective names in your ASP code...

rokslide · #3 (**permalink**) 07-20-04, 19:10

Adding to what Peter said....

Use the vbscript function MonthName. It takes a numeric month representation and returns the string version eg. passing 1 will return January.

Full syntax is.... MonthName(month[, abbreviate])

You could probably write a db function to achieve the same but I'm not really sure how you would go about that... Mmmm good task to try...

Seppuku · #4 (**permalink**) 07-21-04, 01:43

Yup.. create a user defined function.

Code:

CREATE FUNCTION [dbo].[fn_MonthName] (@MonthNum tinyint)  
RETURNS char(3)
AS  
BEGIN 

  RETURN CASE @MonthNum
             WHEN 1 THEN 'Jan'
             WHEN 2 THEN 'Feb'
             WHEN 3 THEN 'Mar'
             WHEN 4 THEN 'Apr'
             WHEN 5 THEN 'May'
             WHEN 6 THEN 'Jun'
             WHEN 7 THEN 'Jul'
             WHEN 8 THEN 'Aug'
             WHEN 9 THEN 'Sep'
             WHEN 10 THEN 'Oct'
             WHEN 11 THEN 'Nov'
             WHEN 12 THEN 'Dec' 
         END
END

Then in your SQL, you can do:

SELECT dbo.fn_MonthName(Month([TransEntryDate])) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY dbo.fn_MonthName(Month([TransEntryDate]));

Internet Services

Web Development

Digital Marketing

Consumer Tech