InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 19:19:29 GMT20http://www.sqlservercentral.com/Forums/Topic758971-338-1.aspxHey Lowell & Matt u both rocks,thanks for your guidanceSun, 26 Jul 2009 22:35:45 GMTwindows_msshttp://www.sqlservercentral.com/Forums/Topic758971-338-1.aspxyour function is fine, it's the function call...since it is returning a table, you need SELECT * FROM, not SELECT:[code]select * from dbo.testFunction(getdate(),getdate()+30);--results:2009-07-27 00:15:08.4602009-07-28 00:15:08.4602009-07-29 00:15:08.4602009-07-30 00:15:08.4602009-07-31 00:15:08.460etc....[/code]Sun, 26 Jul 2009 22:18:09 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic758971-338-1.aspxhi Matt,thanks for your kind reply,when i created that function, and executed it like this,[code="sql"]CREATE FUNCTION dbo.testFunction(@STARTDATE datetime,@EntDt datetime)RETURNS TABLEASRETURN with DateList as ( select cast(@STARTDATE as datetime) DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )GOselect dbo.testFunction(getdate(),getdate()+30);Msg 4121, Level 16, State 1, Line 1Cannot find either column "dbo" or the user-defined function or aggregate "dbo.testFunction", or the name is ambiguous.[/code]guidance needed,thanks in advance.Sun, 26 Jul 2009 21:54:24 GMTwindows_msshttp://www.sqlservercentral.com/Forums/Topic758971-338-1.aspxjust remove the ;[code]CREATE FUNCTION dbo.testFunction(@STARTDATE datetime,@EntDt datetime)RETURNS TABLEASRETURNwith DateList as ( select cast(@STARTDATE as datetime) DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )GO[/code]Let me know if this worksFri, 24 Jul 2009 06:18:20 GMTmatt6288http://www.sqlservercentral.com/Forums/Topic758971-338-1.aspxwhen i compiling the following code, getting this error[b]Msg 102, Level 15, State 1, Procedure fnGetNoOfBusinessDays, Line 6Incorrect syntax near ';'.[/b][code="sql"]CREATE FUNCTION dbo.fnGetNoOfBusinessDays (@STARTDATE datetime,@EntDt datetime)RETURNS TABLE ASRETURN ;with DateList as ( select cast(@STARTDATE as datetime) DateValue union all select DateValue + 1 from DateList where DateValue + 1 < convert(VARCHAR(15),@EntDt,101) )select * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )GO[/code]Fri, 24 Jul 2009 06:08:16 GMTwindows_mss