user3543512 user3543512 - 1 month ago 5
SQL Question

SQL Server stored procedure : return first and last date

Basically I'm going to pass in these multiselect values to my stored procedure:

'April 2016, May 2016, June 2016, July 2016'


Values could be more than 4.

Just how do I return the first and last date of the list using the stored procedure?

Expected output like:

2016-04-01
2016-07-31

Answer

Insert all your rows in temp table and find min and max dates do this in your procedure. if you are passing as comma-separated list make sure it should be converted to rows.

  SELECT CONVERT(DATETIME, 'Apr 2016') date_v
    INTO   #t11
    UNION ALL
    SELECT CONVERT(DATETIME, 'may 2016')
    UNION ALL
    SELECT CONVERT(DATETIME, 'june 2016')
    UNION ALL
    SELECT CONVERT(DATETIME, 'july 2016')

    SELECT Min(date_v) min_date,
           Max(date_v) max_date
    FROM   #t11 

Update: I believe you are passing as comma separated list for this follow below approach

      DECLARE @TOKENS VARCHAR(MAX)='AUG 2016,APR 2016'
DECLARE @list XML

SELECT @list = Cast('<a>' + Replace(@tokens, ',', '</a><a>')
                    + '</a>' AS XML)

SELECT Ltrim(t.value('.', 'varchar(200)'))AS data
INTO   #tr
FROM   @list.nodes('/a') AS x(t)

SELECT Min(CONVERT(DATETIME, data)),
       Max(CONVERT(DATETIME, data))
FROM   #tr 

Updated Post with EOMONTH for max date

 declare @tokens varchar(max)='aug 2016,apr 2016'
declare @list xml

select @list = cast('<a>' + replace(@tokens, ',', '</a><a>')
                    + '</a>' as xml)

select ltrim(t.value('.', 'varchar(200)'))as data
into   #tr1 
from   @list.nodes('/a') as x(t)

select min(convert(datetime, data)),
       eomonth((max(convert(datetime, data))))
from   #tr1 
Comments