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.
| Author |
Topic |
|
jmersing
Yak Posting Veteran
57 Posts |
 Posted - 2010-11-20 : 12:24:10
|
| I'm getting the following error :242: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.It seems to be the else statement that is causing it, any idea how I need to convert this or add some kind of error handling? I'm trying to sum start from end time and then convert it to hours. ThanksCase plannedeventtimeline.activityid When -4113 Then convert(varchar,Dateadd(mi,-30,(Convert(varchar(10),Sum(Datediff(n,plannedeventtimeline.starttime,plannedeventtimeline.endtime))/60) +':' + Convert(varchar(10),Sum(Datediff(n,plannedeventtimeline.starttime,plannedeventtimeline.endtime))%60))),108) Else convert(varchar,Dateadd(mi,-30,(Convert(varchar(10),Sum(Datediff(n,SHIFTASSIGNMENT.starttime,SHIFTASSIGNMENT.endtime))/60) +':' + Convert(varchar(10),Sum(Datediff(n,SHIFTASSIGNMENT.starttime,SHIFTASSIGNMENT.endtime))%60))),108) End As Duration, |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-20 : 12:29:46
|
| Have a look at the strings you are creating.convert(varchar, without a length could be an issue.':; + style 108 (which is a time) also looks dubious.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-11-20 : 12:38:56
|
| I see what you mean I was missing the length on a few. I have them back in now.I wasn't sure what you meant by your second comment, I am trying to work with times in a 00:00 format.The strange thing is if I run the "else" part of this in a seperate query it works? Case plannedeventtimeline.activityid When -4113 Then convert(varchar(10),Dateadd(mi,-30,(Convert(varchar(10),Sum(Datediff(n,plannedeventtimeline.starttime,plannedeventtimeline.endtime))/60) +':' + Convert(varchar(10),Sum(Datediff(n,plannedeventtimeline.starttime,plannedeventtimeline.endtime))%60))),108) Else convert(varchar(10),Dateadd(mi,-30,(Convert(varchar(10),Sum(Datediff(n,SHIFTASSIGNMENT.starttime,SHIFTASSIGNMENT.endtime))/60) +':' + Convert(varchar(10),Sum(Datediff(n,SHIFTASSIGNMENT.starttime,SHIFTASSIGNMENT.endtime))%60))),108) End As Duration, |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-20 : 12:49:02
|
| formatting makes it tricky to understandis it convert(...) + ':' + convert(...,108)or convert(...,108)What is the 108 trying to do - converting a char to a char isn't affected by it.it looks like you might get '1:2' wonder if you are getting a '-5:5' formatAre starttime and endtime datetimes?If so it must be something you are doing with the result as I don't see anything else here that will do a convert.You could put the result into a temp table and look for invalid formats.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-11-20 : 12:57:06
|
| Here is a sample of tyhe result:ID - Date - Starttime - End Time - Duration 882 11-19-2010 07:00:00 15:30:00 08:00:00 884 11-19-2010 07:00:00 17:30:00 10:00:00 885 11-19-2010 12:00:00 20:30:00 08:00:00 886 11-19-2010 08:00:00 17:30:00 09:00:00 887 11-19-2010 06:00:00 14:30:00 08:00:00Maybe there is a better way to calculate this and return it in this format? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-20 : 13:14:21
|
| Do you want the difference betwee starttime and endtime and truncatimg to the hour?I guess starttime and endtime are datetimes?dateadd(hh,datepart(hh,endtime-starttime),0)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-11-20 : 13:32:06
|
| Well it runs now but the format returned looks like this 1900-01-01 09:00:00.000 and it doesnt seem to add in any 15 or 30 minute increments it is always just the hour. Thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-20 : 14:06:47
|
| endtime-starttime gives the differencedatepart(mi,endtime-starttime)/60 excludes the full hours and leave the number of minutes as an integer.datepart(mi,endtime-starttime)/60/15*15 truncates to 15 mins.will give the number of minutessodateadd(mi,datepart(mi,endtime-starttime)/60/15*15,dateadd(hh,datepart(hh,endtime-starttime),0))==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-11-21 : 10:56:52
|
| This seems to work except for the resulting format, can you tell me how to get it into this type of format? 00:00:00Thanks |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-21 : 12:35:26
|
| convert(varchar(8),exp,108)soconvert(varchar(8),dateadd(mi,datepart(mi,endtime-starttime)/60/15*15,dateadd(hh,datepart(hh,endtime-starttime),0)),108)Have a look at convert in bol for the styles==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Error 242