find out every date from first to last day of the month using table valued function

my assignment is to create table-valued function, which will have one DATETIME parameter and which returns every date from first to last day of the month, for example I gave the function date 04/12/2015 from that parameter and it will print all 31 days date of December.

I've already created 2 scalar functions (this was hint in assignment) one calculates first day

CREATE  FUNCTION firstday 
(
 @first DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN CAST(MONTH(@first) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(@first) AS VARCHAR)
END

and second calculates last day:

CREATE  FUNCTION lastday 
(
  @last DATETIME
)
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@last))),DATEADD(mm,1,@last)),101)
END

and here is the table-valued function but I don't know how to use that scalar functions within it

CREATE FUNCTION get_months_dates
(
   @mydate DATETIME
)
RETURNS TABLE
AS
RETURN
WITH Calender AS 
(
SELECT **first_function** AS CalenderDate
UNION ALL
SELECT CalenderDate + 1 FROM Calender
WHERE CalenderDate + 1 <= **last_function**
)
SELECT [Date] = CONVERT(VARCHAR(10),CalenderDate,25) 
FROM Calender
OPTION (MAXRECURSION 0)

I am using sql-server 2014.

Answers


We're not here to do your homework for you, or it defeats the purpose of the homework.

That said, some tips to get you going -

  • Don't build your FirstDay using a CAST to string like that - it's not reliable across different servers with different system settings. Run that on a non-US server and it'd fail. Use DATEADD and DATEPART instead.
  • Look up numbers / tally tables. Combine that with DATEADD and you're away.
  • Functions are called inline like anything else in a SELECT statement - so SELECT Function(Parameters) FROM Table

Good luck!


Try something like this....

Declare @Date DATE = '20151104';

WITH X AS 
(
SELECT TOP (31)
       YEAR(@Date) AS [Year]
       ,MONTH(@Date) AS [Month]
       ,RIGHT('00' + CAST(ROW_NUMBER() 
            OVER (ORDER BY (SELECT NULL)) AS VARCHAR(2)),2) [Days]
FROM master..spt_values
), Dates AS
(
SELECT TRY_CONVERT ( DATE, ( CAST( [Year] AS varchar(4))
                           + CAST( [Month] AS varchar(2))
                           + CAST( [Days] AS varchar(2))) ) DatesVals
FROM X 
)
Select * FROM Dates
Where DatesVals IS NOT NULL

This function will create a list of all dates in a month minus the format. You should be able to add it yourself:

Function:

CREATE FUNCTION f_get_months_dates
(
   @mydate DATETIME
)
RETURNS TABLE
AS
RETURN
WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top (day(EOMonth(@mydate))) 
    dateadd(d, N, EOMonth(@mydate, -1)) CalendarDate
FROM tally
ORDER BY N

Example of how to use function:

SELECT CalendarDate
FROM f_get_months_dates(getdate())

Result:

All dates within the current month


You can try DateAdd function to get all the dates. Here is the example how to do it:

Create FUNCTION dbo.DatesBetween(@mydate date)
RETURNS @dates TABLE (
   DateValue date NOT NULL
) 
AS

BEGIN
declare  @startDate Date= dbo.firstday(@mydate);
declare  @endDate Date=dbo.lastday(@mydate);

   WHILE (@startDate <= @endDate) BEGIN
      INSERT INTO @dates VALUES (@startDate);
      SET @startDate = DATEADD(day, 1, @startDate);
   END;

   RETURN;
END;


select * from dbo.DatesBetween(GETDATE()) ;

Need Your Help

How to scroll the page when a modal dialog is longer than the screen?

css scroll

I have a modal dialog in my app which can get quite long in the y direction. This introduces a problem whereby some of the content of the dialog is hidden off the bottom of the page.

Using legacy COM component in C# multithreaded Environment

c# .net multithreading com legacy

I have legacy COM component, and my mission is to write web service that wrap the COM and enable concurrent non-blocking calls.