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

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

  • error when running any query in sql server management studio: the file exists
  • SSIS Connection Manager Not Storing SQL Password
  • Display top n records and consolidate the rest of the rows
  • Calculating percentile rankings in MS SQL
  • How to split table into multiple tables using SQL
  • Selecting overlapping time ranges
  • 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
    

  • grabbing first result set from a stored proc called from another stored proc
  • Insert Concurrency Issue - Multithreaded Environment
  • how to force a lock to be obtained in SQL Server?
  • SQL stored procedure: how do you use the returned value in the same query?
  • How to get machine name from ip address in SQL Server 2008?
  • Can I use one .mdf file with multiple DBMSes (SQL Server 2008 and 2012)?
  • 2 Solutions collect form web for “Return (Cast? Convert?) values as DateTIme from a Distinct year, month query in a store procedure”

    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?

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.