如何查看最近三个月在数据库中排序的建议?

本文介绍了如何查看最近三个月在数据库中排序的建议?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是一个新的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...