0

am trying to create sql userdefined functionwhen am trying to add two time values ex 09:00:00+18:00:00 shows error value here i get ans like 03:00:00

my code is

declare @StartDate datetime,@EndDate datetime,@hoursInAWorkday time
set @StartDate='10/07/2013 08:00:00'
set @EndDate='10/10/2013 17:00:00'
declare @time1 time = '00:00:00';
declare @time2 time = '09:00:00';

while @StartDate<@EndDate
Begin
set @StartDate=DATEADD(D,1,@StartDate)
set @time2= DATEADD(hh,9,@time1)
set @time1=@time2
End

here 9+9 i got 18 but after that i am getting wrong answers.plz plz help me....

user2819435
  • 1
  • 1
  • This post might help you http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server – Bobby Oct 24 '13 at 08:20

1 Answers1

0

Your code does not produce an error and getting 03:00:00 is correct because you are adding 9 hours every loop

Bobby
  • 2,830
  • 3
  • 19
  • 36
  • For example 6pm + 9 hours is 3am – Bobby Oct 24 '13 at 08:29
  • ok..plz help me how to convert date time to varchar..i need to set a datetime to variable of type varchar – user2819435 Oct 24 '13 at 12:21
  • `code` DECLARE @StartDate DATETIME SET @StartDate='10/07/2013 08:00:00' SELECT CONVERT(VARCHAR, @StartDate) `code` This will give you "Oct 7 2013 8:00AM" – Bobby Oct 25 '13 at 13:40