Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

List the dates for the Month...?

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
u2kim
Asked:
u2kim
2 Solutions
 
xenon_jeCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
u2kimAuthor Commented:
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
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.

 
Scott PletcherSenior DBACommented:
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
 
xenon_jeCommented:
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
 
ispalenyCommented:
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
 
AndrewNovickCommented:
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
 
CleanupPingCommented:
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
 
monosodiumgCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now