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