如何查看最近三个月在数据库中排序的建议? [英] How to view the Suggsetions sorted in the Database for the last three months?

问题描述

我是一个新的ASP.NET开发人员，我正在尝试开发一个简单的建议框系统.我的数据库设计包含以下部分:

>用户表:用户名，名称，部门代码...等
>
>分区表:SapCode，分区
>
>建议日志表:ID，标题，描述，submittedDate，用户名

***(第一个属性是每个表中的主键，属性(submittedDate)属于DateTime数据类型)***

现在，我需要开发一个表，显示最近三个月的建议.我已经开发了一个查询，其中显示了员工姓名，用户名，部门，建议标题，建议说明.我现在想要的只是显示月份.例如，要显示最近三个月的建议，月"列应显示:2012年1月，2011年12月，2011年11月 **那么该怎么做?**

我当前的SQL查询:

I am a New ASP.NET Developer and I am trying to develop a simple suggestion box system. I have the following part of my database desing:

> User Table: Username, Name, DivisionCode... etc
>
> Division Table: SapCode, Division
>
> SuggestionLog Table: ID, Title, Description, submittedDate, Username

***(The first attribute is the primary key in each table and the attribute (submittedDate) is of DateTime data type)***

Now, I need to develop a table that shows suggestions for the last three months. I already developed a query that shows the Employee Name, Username, Division, Suggestion Title, Suggestion Description. All what I want now is to show the Month. For example, to show the suggestions for the last three months, the Month column should show: Jan-2012, Dec-2011, Nov-2011 **So how to do that?**

My current SQL query:

SELECT     dbo.SafetySuggestionsLog.Title, dbo.SafetySuggestionsLog.Description, dbo.SafetySuggestionsType.Type, dbo.SafetySuggestionsLog.Username, 
                          dbo.employee.Name, dbo.Divisions.DivisionShortcut
    FROM         dbo.Divisions INNER JOIN
                          dbo.employee ON dbo.Divisions.SapCode = dbo.employee.DivisionCode INNER JOIN
                          dbo.SafetySuggestionsLog ON dbo.employee.Username = dbo.SafetySuggestionsLog.Username INNER JOIN
                          dbo.SafetySuggestionsType ON dbo.SafetySuggestionsLog.TypeID = dbo.SafetySuggestionsType.ID

所需的输出将显示:

**员工名称，用户名，部门，SuggestinationTitle，SuggstionDescription，SuggestionType Month(submissionDate)**

The desired output is to display:

**Employee Name, Username, Division, SuggestionTitle, SuggstionDescription, SuggestionType Month(submissionDate)**

推荐答案

在查询的选择部分中包含子句CONVERT(CHAR(4), submittedDate, 100) + "-" + CONVERT(CHAR(4), submittedDate, 120) .这将为您提供适当的格式化值.

Include the clause CONVERT(CHAR(4), submittedDate, 100) + "-" + CONVERT(CHAR(4), submittedDate, 120) in the select part of your query. This will give you the appropriate formatted values.

您好...
在您的查询中尝试此操作，只需在您的选择语句中添加

hi there...
try this in ur query, just add in your select statement

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11),GETDATE(),106),8),' ','-') AS 'MonthDate'

现在你会像2012年3月，2012年2月，2012年1月
希望这会有所帮助..
请回复您的评论...

now u will get like mar-2012,feb-2012,jan-2012
Hope this helps..
plz revert back with ur comments...

这篇关于如何查看最近三个月在数据库中排序的建议?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！