Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Question Unanswered: ASP page with SQL query help needed...

    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!

  2. #2
    Join Date
    Apr 2004
    Posts
    50

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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]));
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •