从存储过程中的不同年月查询返回(转换?转换?)值作为 DateTIme [英] Return (Cast? Convert?) values as DateTIme from a Distinct year, month query in a store procedure

问题描述

我有一个带有出版物表的数据库，该表可以通过 SubPublications 表连接到自身

I have database with a Publications table that is many-to-may joined to iself through a SubPublications table

我的存储过程从指定类型的出版物的 ReleaseDate 字段返回所有不同的 Year-Month 组合，这些字段与特定(按 id)出版物无关(因此有 2 个参数，见下文).

My stored procedure returns all of the distinct Year-Month combos from a ReleaseDate field of Publications of a specified type that are not related to a specific (by id) publication (hence the 2 params, see below).

问题:
proc 工作正常，但我希望返回列类型为 DateTime2，虚拟日期为 1.就像现在一样，它返回 2 列整数.我该怎么做?

我知道我可以在我的应用程序代码中进行转换，但我更愿意将它作为日期时间从数据库交付.

I know I could do the conversion in my app code, but I'd rather have it delivered as a datetime from the DB.

我的 SQL 不是很好.我什至不知道我应该使用强制转换还是转换.

My SQL ain't great. I don't even know if I should use a cast or a convert.

我在网上找不到在这样的查询中转换回日期时间的示例.任何人都可以帮忙吗?这是我写的 proc，就目前而言:

I can't find an example online of converting back to datetime within a query like that. Can anyone help? Here's the proc I wrote, as it stands:

ALTER PROCEDURE sp_DistinctPubMonthYears 
    @PubType char(1),
    @PubId int = 0      
AS
BEGIN
SELECT 
    DISTINCT TOP (100) PERCENT 
        DATEPART(month, ReleaseDate) AS month, 
        DATEPART(year, ReleaseDate) AS year         
    FROM(         
        SELECT 
            Publications.ReleaseDate AS ReleaseDate,
            Publications.PublicationId As PubId,
            Publications.PubType AS PubType,
            SubPublications.PublicationId AS ParentId
        FROM 
            Publications LEFT JOIN SubPublications
        ON 
            Publications.PublicationId = SubPublications.PublicationId 
        WHERE
            Publications.PubType = @PubType AND
            Publications.PublicationId <> @PubId AND
            (
                SubPublications.PublicationId <> @PubId OR  
                /*either it's parent is NOT the one we're searching on or */
                SubPublications.PublicationId IS NULL  
                /*or it's not joined to anything at all */
            )               
    ) AS sub
ORDER BY year ASC, month ASC 
END
GO

推荐答案

你不需要 TOP，你也可以 ORDER BY 表达式.

You don't need TOP and you may as well ORDER BY the expression.

这个 DATEADD/DATEDIFF 表达式 会给你当月的开始

This DATEADD/DATEDIFF expression will give you start of current month

SELECT DISTINCT
    CAST(
      DATEADD(month, DATEDIFF(month, 0, ReleaseDate), 0) AS datetime2
    ) AS myCol 
FROM(         
...
ORDER BY
    DATEADD(month, DATEDIFF(month, 0, ReleaseDate), 0)

正如 Faust 提到的，如果您愿意，我们可以使用别名进行订购.

As Faust mentioned, we can order on the alias if you prefer.

...
ORDER BY
    myCol

在这种情况下，结果是一样的.

In this case the result is the same.

如果 CAST 是 varchar，那么你会得到不同的结果.这就是为什么我倾向于使用表达式而不是别名，但它非常简单.我当然会测试我的更改...，不是吗?

If the CAST was to varchar then you would have different results. This is why I tend to use the expression not the alias but it's quite trivial. Surely I'd test my changes..., no?

这篇关于从存储过程中的不同年月查询返回(转换?转换?)值作为 DateTIme的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！