updating table using table valued fnc??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

updating table using table valued fnc???

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2011-12-23 : 06:20:13

--I am trying to update my table using the following procedure however an error occurs.

select observation FROM observationfun ('0103',(select bus from CDSClient_Monthly_kons))

-- where '0103' is 'month-day' of when the observation should start and bus is a date to which the year shoud be added:

Create table #tmp_portfolio (ID int not null identity, bus datetime null,observation datetime null, refdate datetime null)
Insert into #tmp_portfolio (bus)
Values ('6/1/2010')
Insert into #tmp_portfolio (bus)
Values ('7/1/2009')
Insert into #tmp_portfolio (bus)
Values ('8/5/2011')
Insert into #tmp_portfolio (bus)
Values ('10/16/2011')
Insert into #tmp_portfolio (bus)
Values ('11/18/2011')
Insert into #tmp_portfolio (bus)
Values ('11/19/2011')
Insert into #tmp_portfolio (bus)
Values ('11/19/2011')

CREATE FUNCTION [GLOW001\JD22832].[observation] (@startDate char(4), @bus datetime)
RETURNS @ObservationTable TABLE
(
[observation] datetime,
[refdate] datetime
)
AS
BEGIN
DECLARE @RptDate DATETIME
IF DATEDIFF(Day,CONVERT(datetime, LEFT(CONVERT(char(4),2008),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2)), @bus) between 0 AND 360
SET @RptDate = CONVERT(datetime, LEFT(CONVERT(char(4),2008),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2))
IF DATEDIFF(Day,CONVERT(datetime, LEFT(CONVERT(char(4),2009),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2)), @bus) between 0 AND 360
SET @RptDate = CONVERT(datetime, LEFT(CONVERT(char(4),2009),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2))
IF DATEDIFF(Day,CONVERT(datetime, LEFT(CONVERT(char(4),2010),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2)), @bus) between 0 AND 360
SET @RptDate = CONVERT(datetime, LEFT(CONVERT(char(4),20102),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2))
IF DATEDIFF(Day,CONVERT(datetime, LEFT(CONVERT(char(4),2011),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2)), @bus) between 0 AND 360
SET @RptDate = CONVERT(datetime, LEFT(CONVERT(char(4),2011),4)+LEFT(CONVERT(char(4),@startDate),2)+RIGHT(CONVERT(char(4),@startDate),2))
INSERT @ObservationTable
VALUES(@RptDate,DATEADD(ms,-3,DATEADD(yy,1,@RptDate)))
RETURN
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 06:41:11

the syntax of function call is wrong

it should be


SELECT t.*,f.observation 
FROM CDSClient_Monthly_kons t
CROSS APPLY  dbo.observationfun ('0103',t.bus)f

for update it should be


UPDATE t
SET t.Yourfield=f.observation 
FROM CDSClient_Monthly_kons t
CROSS APPLY  dbo.observationfun ('0103',t.bus)f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-23 : 06:53:22

Thanks, but there s the following error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 07:01:32

where are you passing date values?
is '0103' represenating date? if yes, you cant convert it to date directly as it doesnt confirm to any of standard date formats

Read below to understand on how to avoid this problem while passing date values in sql server

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-23 : 07:13:40

Normally, it works fine:

select CONVERT(datetime, LEFT(CONVERT(char(4),2011),4)+LEFT(CONVERT(char(4),'0103'),2)+RIGHT(CONVERT(char(4),'0103'),2))

select DATEDIFF(Day,(CONVERT(datetime, LEFT(CONVERT(char(4),2001),4)+LEFT(CONVERT(char(4),'0103'),2)+RIGHT(CONVERT(char(4),'0103'),2))), CAST('2006-12-01 00:00:00.000' as datetime))

rasta
Starting Member

23 Posts

Posted - 2011-12-23 : 07:15:43

However, thanks to your blog page I found there s mistake> instead of 2010 having 20102. Now it works excellent!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 07:23:46

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rasta
Starting Member

23 Posts

Posted - 2011-12-23 : 07:58:20

One more question, how do I call the function in the WHERE statment?

Select p1.bus,p2.observation from table p1 inner join table p2 on .......
where DATEDIFF(Day,dbo.observationfun ('0103',t.bus), p2.bus) <= 65

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 10:51:35

nope. you cant call this function in WHERE condition . it has to come in a apply statement or inside a subquery as function is table valued

so it should be like


Select p1.bus,p2.observation from table p1 inner join table p2 on ....... 
cross apply dbo.dbo.observationfun ('0103',t.bus) f
where  DATEDIFF(Day,f.<required field>, p2.bus) <= 65

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources