How to find date of Month

Hi

Problem:

one table "tbl_SalesReporting" in this table salesEr every day submit his information.

i need which day salesEr not submit his information. this is check up to current date.

E.G:

salesEr submit information start this date "1/04/2006" to "17/04/2006" and

currentdate: 20/04/2006

i need 18/04/2006,19/04/2006 this date Using SQL queries......

Please help me......

In SQL Server 2005, I use a CTE to hold all dates up to today. Then find out which date is not in your list.

Here is the code:

With reportMonthdatesCTE(mydates)
AS
(select dateadd(ms,+3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

union all
select dateadd(d, 1, mydates) from reportMonthdatesCTE WHERE Month(mydates)=Month(getdate()) and mydates<=getdate()

)
SELECT convert(nchar(8),mydates, 112) as salesEr FROM reportMonthdatesCTE WHERE convert(nchar(8),mydates, 112) not in
(SELECT convert(nchar(8),salesEr, 112)
FROM tbl_SalesReporting
WHERE Month(salesEr)=Month(getdate()) and salesEr<=getdate())

|||

Hi dear......

i m use SQL server 2000 so please help me......

|||Jayu, my suggestion is to create a "Calendar" table which holds all of the dates for the year. Then you can run a query from to see which dates in your tbl_SalesReporting table are missing. Assuming the data types of your columns are DateTime, the query would look something like this:

SELECT *FROM tbl_SalesReportingWHERE salesERDateBETWEEN @.monthStartDateand @.CurrentDateANDCONVERT(char(8),salesErDate,112)NOT IN (SELECT calendardateFROM CalendarWHERE calendarDateBETWEEN @.monthStartDateAND @.CurrentDate)

|||

Jayu:

You can build a calendar table in your database or create a UDF in SQL server 2000. I did this for you. Please check it to see whether there is any problem.

Here is your solution:

Part 1: Create a function to hold the partial calendar:

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

go

CREATE

function [dbo].[monthCalendar](@.Checkdatedatetime)

returns

@.monthCalendartable( DatesInMonthdatetime

)

as

BEGIN

declare

@.dint

declare

@.maxdayint

set

@.d=0

set

@.maxday=Day(@.Checkdate)

while

@.d< @.maxday

BEGIN

insert

@.monthCalendar(DatesInMonth)

select

dateadd(ms,+3,DATEADD(mm,DATEDIFF(mm,0,@.Checkdate), @.d))

set

@.d=@.d+1

END

--end of the loop for construction the calendar table

RETURN

END

Part 2: query part:

SELECT

convert(nchar(8),DatesInMonth, 112)as salesErFROM [dbo].[monthCalendar](getdate())WHEREconvert(nchar(8),DatesInMonth, 112)notin

(

SELECTconvert(nchar(8),salesEr, 112)

FROM

tbl_SalesReporting

WHERE

Month(salesEr)=Month(getdate())and salesEr<=getdate())

I like 2005 version's solution. Enjoy.

|||

Hi

thanks for reply

i have create UDF function and run this query

SELECT convert(nchar(8),DatesInMonth, 112) as SalesEr
FROM [dbo].[monthCalendar] (getdate())
WHERE convert(nchar(8),DatesInMonth, 112)
not in
(SELECT convert(nchar(8),SalesEr, 112)
FROMtbl_SalesReporting
WHERE Month(SalesEr)=Month(getdate()) and SalesEr<=getdate())

but i m fashing error.......

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.

Please help me.........

|||

Hilimno

many many Thanks

solve my problems..........

again thanks.......

|||

Hi Limno

I can build a calendar table in your database or create a UDF in SQL server 2000. this is execute successfully

but i require this type

i pass date into the query and i require date (which is not enter in database) between this date and current date.

e.g:

Startdate:20/4/2006

Enddate: currentDate suppose 03/05/2006

database table name "tbl_Sales" and datefield Name "entryDate"

entryDate: 20/4/2006 ,21/4/2006,22/4/2006,23/4/2006,24/4/2006,26/4/2006,27/4/2006,28/4/2006,29/4/2006

30/4/2006,01/5/2006

i require this date

25/4/2006, 02/5/2006, 03/5/2006

Please help me.........

|||

You need a customized calendar for all dates in a year.

Here is the modified solution for your case now:

SELECT

convert(nchar(8),Dates, 112)as salesErFROM [dbo].[yearCalendar]WHERE

(

CONVERT(nchar(8), Dates, 112)>='20060420'and Dates<=getdate())AND

convert

(nchar(8),Dates, 112)notin

(

SELECT

convert(nchar(8),salesEr, 112)

FROM

tbl_SalesReporting

WHERE

CONVERT(nchar(8), salesEr, 112)>='20060420'and salesEr<=getdate())

If you need to use this query often, you should convert it into a Stored Procedure with a datetime parameter.

Notice the date convert function here. You can check Book Online to choose the format you like most. What I am using here is ISO standard yyyymmdd.

Here is the code for the calendar table:

USE

mytest--your table

GO

CREATE

table yearCalendar(Datesdatetime)

GO

declare

@.nint

declare

@.maxnint

declare

@.begindatedatetime

set

@.n=1

set

@.maxn=365-- Number of days added to the calendar

set

@.begindate=convert(datetime,'01/01/2006')-- Initial date for the first run is todays date

set

@.begindate=@.begindate-1

while

@.n<= @.maxn

begin

insert

into yearCalendar(Dates)select @.begindate+@.n

set

@.n=@.n+1

end

GO

--Source:http://www.databasejournal.com/features/mssql/article.php/3502256

|||

Hi Dear.......

it's working ......

Thanks a lot dear........