Trying to get an End Time - 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)

Trying to get an End Time

Author

Topic

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 08:08:19

I'm trying to get an end time by combining my starttime field and my avgruntime....problem i'm having is the time is set as hhmmss...the avg run time is in minutes. So if the user enters 120 as the avg run time it's 2 hours...but if the user enters 45 its only 45 minutes...so how do you get it to change the hours when hours and the minutes when minutes?

SELECT JobId,JobName,
CAST(year(startdate) AS varchar)+'-' + CAST(month(startdate) AS varchar)+'-' + CAST(day(startdate) AS varchar) 
+' ' + CAST(CONVERT(NVARCHAR, cast((StartTime/ 10000) as varchar(10)) + ':' + right('00' + cast(StartTime % 10000 / 100 as varchar(10)),2) )AS varchar) AS StartDay,

CAST(year(enddate) AS varchar)+'-' + CAST(month(enddate) AS varchar)+'-' + CAST(day(enddate) AS varchar) 
+' ' + CAST(CONVERT(NVARCHAR, cast((StartTime / 10000)+(avgruntime/60) as varchar(10)) + ':' + right('00' + cast(((StartTime % 10000 / 100)+(avgruntime) ) as varchar(10)),2))AS varchar) AS EndDay,
startdate,EndDate,avgruntime
from jobs where active=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 08:20:08

[code]
SELECT JobId,JobName,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,1,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,1,0,':') AS datetime)) AS EndDate
FROM Table
[/code]

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 09:18:20

I'm sorry i don't quite understand what you did there. Would you mind elaborating? Thanks for the quick response by the way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:00:24

quote:
Originally posted by phqu88

I'm sorry i don't quite understand what you did there. Would you mind elaborating? Thanks for the quick response by the way.

did it work for you?
what i've done is append startime to date value as a string and then convert them to datetime
so if its today, the date part will have 20081216 i add hhmmss to it after adding : in between so if value was 101535 it will become 10:15:35 and date value will be 20081216 10:15:35 which will be start date
for end date i just add avgruntime to it using DATEADD with minutes as factor (as avgruntime value is in minutes)

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:04:27

quote:
Originally posted by visakh16

quote:
Originally posted by phqu88

I'm sorry i don't quite understand what you did there. Would you mind elaborating? Thanks for the quick response by the way.

did it work for you?
what i've done is append startime to date value as a string and then convert them to datetime
so if its today, the date part will have 20081216 i add hhmmss to it after adding : in between so if value was 101535 it will become 10:15:35 and date value will be 20081216 10:15:35 which will be start date
for end date i just add avgruntime to it using DATEADD with minutes as factor (as avgruntime value is in minutes)

Haha well for 1 the stuff function threw me off...i've never seen that and im still trying to get my wings in sql. As for the query, it didn't work...i'm getting an error "Msg 174, Level 15, State 1, Line 10
The stuff function requires 4 argument(s)."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:10:14

it was a copy paste typo. try this & see


SELECT JobId,JobName,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime)) AS EndDate
FROM Table

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:16:33

quote:

Originally posted by visakh16

it was a copy paste typo. try this & see


SELECT JobId,JobName,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CAST(DATEADD(dd,DATEDIFF(dd,0,startdate),0) as varchar(12)) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime)) AS EndDate
FROM Table

Now it's throwing this error...
Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:21:28

[code]
SELECT JobId,JobName,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0) ,121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime)) AS EndDate
FROM Table
[/code]

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:25:40

quote:

Originally posted by visakh16


SELECT JobId,JobName,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0) ,121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime)) AS EndDate
FROM Table

it's giving me that same error...would this be because i was trying to combine a datetime column with an int column...the startdate being the datetime and the starttime being the int which is in hhmmss format?

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:29:50

quote:
Originally posted by phqu88

quote:
Originally posted by visakh16

SELECT JobId,JobName,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate,
DATEADD(mi,avgruntime,CAST(CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0) ,121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime)) AS EndDate
FROM Table

it's giving me that same error...would this be because i was trying to combine a datetime column with an int column...the startdate being the datetime and the starttime being the int which is in hhmmss format?

nvm after reading your explanation again that shouldn't have anything to do with it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:33:22

SELECT CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate
FROM Table

what does this return you?

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:38:34

quote:
Originally posted by visakh16

SELECT CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate
FROM Table

what does this return you?

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:38:34

quote:
Originally posted by visakh16

SELECT CONVERT( varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') AS datetime) as StartDate
FROM Table

what does this return you?

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:42:15

[code]SELECT CONVERT(varchar(11),DATEADD(dd,DATEDIFF(dd,0,startdate),0),121) + ' ' + STUFF(STUFF(starttime,3,0,':'),6,0,':') as StartDate
FROM Table[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-16 : 10:45:20

Visakh, I think you have forgotten the REPLACE function.
STR(12, 6) yields ' 12' and stuff upon that gives ' : :12' which is not a valid time format.

SELECT	JobID,
	JobName,
	DATEADD(DAY, DATEDIFF(DAY, 0, StartDate), 0) + CAST(STUFF(STUFF(REPLACE(STR(StartTime, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':') AS DATETIME) AS StartDay,
	DATEADD(DAY, DATEDIFF(DAY, 0, EndDate), 0) + DATEADD(MINUTE, AvgRunTime, CAST(STUFF(STUFF(REPLACE(STR(StartTime, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':') AS DATETIME)) AS EndDay
FROM	Jobs
WHERE	Active = 1

E 12°55'05.63"
N 56°04'39.26"

phqu88
Starting Member

9 Posts

Posted - 2008-12-16 : 10:52:56

Peso's query worked like a champ.
Thanks guys for helping me with this, helped me out alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources