Return (Cast? Convert?) values as DateTIme from a Distinct year, month query in a store procedure

Question

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

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).

QUESTION:
The proc works fine, but I want the return column type as DateTime2 with a dummy date of 1. As it is now, it returns 2 columns of integers. How do I do this?

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

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

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

consider not naming your stored procedures prefixed with `sp_`. See this: http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/ — Tahbaza, May 22 '11 at 16:00

score 1 · Answer 1 · answered May 22 '11 at 15:38

DATEADD(MONTH, DATEDIFF(MONTH, '1600-01-01T00:00:00', ReleaseDate), '1600-01-01T00:00:00') should get you your yyyy-MM-dd 00:00:00 date. 1600-01-01T00:00:00 is just an arbitrary date that is best picked to be prior to any dates you may be storing in your ReleaseDate column.

score 1 · Accepted Answer · edited May 23 '17 at 12:19

1

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

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)

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

...
ORDER BY
    myCol

In this case the result is the same.

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?

edited May 23 '17 at 12:19

Community

1
1

answered May 22 '11 at 15:41

gbn

422,506
82
585
676

is there any reason to use the explicit expression rather than the "myCol" alias in the order by clause? – Faust May 23 '11 at 06:50
@Faust: no reason. Just copy/paste. And updated answer, Cheers – gbn May 23 '11 at 06:54

Return (Cast? Convert?) values as DateTIme from a Distinct year, month query in a store procedure

2 Answers2

Linked