CODEWITHSUNDEEP
sqlsql-serverdatestored-procedures
user3543512
user3543512

Reputation: 143

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

Upvotes: 0

Views: 558

Answers (2)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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

Upvotes: 0

Cato
Cato

Reputation: 3701

try this one -

1 it changes to XML,

2 reads from XML document,

3 changes text to dates (hopefully! may be system dependency there)

4 selects min and max, converts to last day of month on max

5 displays in date order

declare @list nvarchar(max) = 'April 2016, May 2016, June 2016, July 2016';

DECLARE @XML nvarchar(max) = '<nub><rec><dat>' + REPLACE(@list, ',', '</dat></rec><rec><dat>') + '</dat></rec></nub>';

select @xml;

DECLARE @iX INT
EXEC sp_xml_preparedocument @ix output, @xml;


WITH CTE AS (SELECT CAST('01 ' + dat AS DATETIME) dat_sort ,dat dat  FROM OPENXML(@ix, '//rec', 2) 
WITH (dat varchar(50))),
MIND AS (select MIN(dat_sort) M from cte UNION SELECT dateadd(day, -1,dateadd(month, 1, MAX(dat_sort))) M from cte)
SELECT M from MIND ORDER BY M

--this is needed to avoid memory leak
EXEC sp_xml_removedocument @iX

Upvotes: 1

Related Questions