?
Solved

List the dates for the Month...?

Posted on 2003-03-18
10
Medium Priority
?
306 Views
Last Modified: 2008-03-10
Hi

I need a SQL query in that
"i need to display all the dates for the given "month and year" else given duration"
How its possible thro SQL query?


0
Comment
Question by:u2kim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 9

Accepted Solution

by:
xenon_je earned 252 total points
ID: 8158004
select * from tblx
where
  datepart(yy,datefield)=1998
  and datepart(mm, datefield) = 6

So try use the datepart function. In my example tablex is your table and datefield is the name of the field that contains date information.

The 'else' part I didn't understand in your question. Can you explain it better, please?

              xenon
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 248 total points
ID: 8159252
If I understand correctly something like this could do it:


DECLARE @startDate SMALLDATETIME
DECLARE @endDate SMALLDATETIME
SET @startDate = '2003-03-01'
SET @endDate = '2003-03-31'

IF OBJECT_ID('tempdb..#dates') IS NOT NULL
     DROP TABLE #dates
CREATE TABLE #dates (date SMALLDATETIME)

SET NOCOUNT ON
WHILE @endDate >= @startDate
BEGIN
     INSERT INTO #dates VALUES(@startDate)
     SET @startDate = @startDate + 1
END --WHILE

SET NOCOUNT OFF
SELECT * FROM #dates
0
 

Author Comment

by:u2kim
ID: 8159493
in my table i have a Date filed and the employee code. But for the monthly report i need all the date for the month.But in the table all the date wont be there(bcas of sat and sundays). but i need the result in the Query like follows....(while i pass the MONTH and YEAR value)

01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
04-01-2003     Mr.X
05-01-2003     Mr.X
.
.
.
30-01-2003     Mr.X
31-01-2003     Mr.X

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8159615
My code will put all the days for a given date range into a temp table.  You could then do a cross join to that table to produce the results you want.
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8159706
Now I'm realy puzzled.
So you have a table like this:

date           code
01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
06-01-2003     Mr.X ---5 and 6 misses because is Saturday&Sunday
...
31-01-2003     Mr.X

And you want a select that filles up the gap and obtaines:
01-01-2003     Mr.X
02-01-2003     Mr.X
03-01-2003     Mr.X
04-01-2003     Mr.X
05-01-2003     Mr.X
06-01-2003     Mr.X ---5 and 6 misses because is Saturday&Sunday
...
31-01-2003     Mr.X

That's what you want????
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8160724
create function dbo.ufn_DaysRange(@From datetime,@Till datetime)
returns @t table (DayInRange datetime primary key clustered) with schemabinding as begin
declare @Count   int set @Count=datediff(Day,@From,@Till)+1
declare @Counter int set @Counter=0
while @Counter<@Count begin
 insert @t(DayInRange) values (dateadd(Day,@Counter,@From))
 set @Counter=@Counter+1
end
return
end
GO

Good luck !
0
 
LVL 1

Expert Comment

by:AndrewNovick
ID: 8161703
What you need is three UDFs that I've include below.

udf_DT_DaysTAB gives you a resultset with one row for each day in a range.  The other two UDFs are used to construct the start date and end date from the month and year number.


Given that you have the month and year then your script would be something like:

DECLARE @Year int       , @Month int
SELECT @Year = 2002     , @MOnth = 11

DECLARE @MonthStartDATE datetime
      , @MonthEndDATE datetime

SELECT @MonthStartDATE = dbo.udf_DT_FromYMD (@Year, @Month, 1)

SELECT @MonthEndDATE = dbo.udf_DT_MonthEndDATE (@MonthStartDATE)

select * from udf_DT_DaysTAB (@MonthStartDate, @MonthEndDate)
GO

It's possible to roll the three functions together so you don't have to make the more complex call.  

Since I'm making these public, I'll publish them in my free T-SQL UDF of the week Newsletter.  You can subscribe at:
http://www.NovickSoftware.com/UDFofWeek/UDFofWeekSignup.htm


Hope that helps.

Regards,
Andy

Andrew Novick
http://www.NovickSoftware.com




SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_DT_DaysTAB (

    @dtFrom datetime -- The first date to consider
  , @dtTo datetime -- The last date to consider
)   RETURNS  @Days TABLE  ( -- Days in range
         [Date] datetime -- The day in question
        )
/*
* Returns one row for each day that falls in a date range.
* Each date is the SOD.
*
* Example:
select * FROM udf_DT_DaysTAB('2002-1-1', '2002-2-3')
*      
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/

AS BEGIN

   DECLARE @WorkDT smalldatetime -- Date we're working with

    -- Start by eliminating the time portion of the start date.
    SET @WorkDT =  DATEADD(dd
                            , DATEPART(dy, @dtFrom) -1
                            , CONVERT(datetime
                                    , CONVERT(char(4)
                                         , DATEPART(yyyy,@dtFrom)
                                             )  
                                      + '-01-01'
                                     )
                             )

    -- Insert one record for each day
    WHILE @WorkDT <= @dtTo BEGIN

        INSERT INTO @Days VALUES (@WorkDT)
        SET @WorkDT = DATEADD (dd, 1, @WorkDT)

    END
   
    RETURN
END
GO
GRANT SELECT on dbo.udf_DT_DaysTAB to PUBLIC
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER      FUNCTION dbo.udf_DT_FromYMD (
                    
    @Year as int -- 4 place year, ie 2002
  , @Month as int -- Month Number 1 to 12
  , @Day as int -- Day of the Month 1 to 31 or what ever
)   RETURNS SMALLDATETIME -- Date from the 3 parameters
    WITH SCHEMABINDING -- to allow determinism
/*
* Returns a smalldatetime given the Year, Month and Day of
* the month.
*  
* Example:
select  dbo.udf_DateFromYMD(2002, 2, 14) -- Valentines Day
*
* Test:
SELECT 'Test 1 ' + case when dbo.udf_DT_FromYMD(2002,2, 14) =
                CONVERT(smalldatetime,'2002-02-14')
                then 'Worked' else 'ERROR' END
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/
AS BEGIN

   RETURN CONVERT (SMALLDATETIME
                   , CONVERT(CHAR(4), @Year)
                    + '-' + CONVERT(VARCHAR(2), @Month)
                              + '-' + CONVERT(VARCHAR(2), @Day)
                   , 110 -- required for determinism
                   )
END
GO
GRANT EXEC on dbo.udf_DT_FromYMD to PUBLIC
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION udf_DT_MonthEndDATE (
   
    @Date as datetime -- Date you want the end of
)   RETURNS SMALLDATETIME -- Start of day on last day of month
/*
* Returns the start of day on the last day of the month that
* @Date falls in.
*
* Example:
SELECT dbo.udf_DT_MonthEndDATE ('2002-02-14 12:34:33')
*
* Test:
PRINT 'Test 1 ' + CASE WHEN '2002-02-28' =
         dbo.udf_DT_MonthEndDATE ('2002-02-14 12:24:44')
            THEN 'WORKED' ELSE 'ERROR' END
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
****************************************************************/
AS BEGIN

DECLARE @TheDate smalldatetime

SET @TheDate = CONVERT(SMALLDATETIME
                              , LEFT(CONVERT(char(10)
                                            , @Date, 20)
                                    , 10)
                                )
     
RETURN DATEADD (day, -1,
            DATEADD(month, 1,
                DATEADD(day
                      , (-1 * DATEPART(day, @TheDate )) + 1
                      , @TheDate
                        )
                    )
               )
END
go

grant exec on dbo.udf_DT_MonthEndDATE to PUBLIC
GO




0
 

Expert Comment

by:CleanupPing
ID: 9275764
u2kim:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183445
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8158004, ScottPletcher http:#8159252

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question