Help related to Time format - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help related to Time format

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-13 : 02:24:32

Dear All,

I am using this query to get updated.

"
update dbo.TMSPublishSchedule set LastSchedule = dbo.dateonly(getdate()) + Tue from dbo.TMSPublishSchedule
where Country_ID='VN' "

And here in the table TMSPublishSchedule the data type of the Lastschedule is "datetime".

The function which already was there is "Dateonly:

create function DateOnly(@DateTime DateTime)
-- Strips out the time portion of any dateTime value.
returns datetime
as
begin
return dateadd(dd,0, datediff(dd,0,@DateTime))
end

But while updating i am getting the error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Please help me to resolve this.

Thanks,
Gangadhar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-13 : 02:30:32

quote:
Originally posted by gangadhara.ms

"
update dbo.TMSPublishSchedule set LastSchedule = dbo.dateonly(getdate()) + Tue from dbo.TMSPublishSchedule
where Country_ID='VN' "

Whats the datatype for the column Tue ?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-13 : 02:38:53

Its from temp table
CREATE TABLE #week (DayNo int, DayName varchar(20))
INSERT #week values (1, 'Sun')
INSERT #week values (2, 'Mon')
INSERT #week values (3, 'Tue')
INSERT #week values (4, 'Wed')
INSERT #week values (5, 'Thu')
INSERT #week values (6, 'Fri')
INSERT #week values (7, 'Sat')

DECLARE @DayWeek int
DECLARE @DayName varchar(10)
DECLARE @SQL varchar(2000)
DECLARE @ToPublishToday varchar(10)

select @DayWeek = DATEPART(dw, GETDATE())
select @DayName = DayName from #week where DayNo = @DayWeek

And update query is this.
-- VN
set @SQL = 'DECLARE @ToPublishToday varchar(10) '
set @SQL = @SQL + 'select @ToPublishToday = ' + @DayName + ' from dbo.TMSPublishSchedule where Country_ID=''VN'' '
set @SQL = @SQL + 'IF len(@ToPublishToday)>1 '
set @SQL = @SQL + ' update dbo.TMSPublishSchedule set LastSchedule = dbo.dateonly(getdate()) + ' + @DayName + ' from dbo.TMSPublishSchedule where Country_ID=''VN'' '
-- print @SQL
exec(@SQL)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-13 : 02:59:18

Try with casting

cast(dbo.dateonly(getdate()) as varchar(25))+ tue

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-07-13 : 03:32:33

Still i have the same error update dbo.TMSPublishSchedule set LastSchedule = cast(dbo.dateonly(getdate()) as varchar(25)) + tue
from dbo.TMSPublishSchedule where Country_ID='VN'

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-13 : 04:36:48

is the column LastSchedule is varchar?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources