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

  • Web service task throwing an error on 2015 data tools
  • Recommended way to create tables in ASP.Net Forms project when using EF
  • Selecting overlapping time ranges
  • What is the correct datatype for an EWL-validated Phone Number field?
  • Help needed in AdventureWorks in a sql query
  • Can someone help me simplify/speed up this Function?
  • 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
    

  • Primary and Foreign Key at the same time
  • Generate Backup Of Table Through SQL Query
  • Switch between databases, use two databases simultaneously
  • SqlConnection SqlCommand SqlDataReader IDisposable
  • How do I join these two together? Varchar guid and guid type both primary keys
  • groupby in view of sql returns aggregate error
  • 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.