June 3, 2015 at 7:48 am
declare @StartTime nvarchar(10)= '12:00'
declare @EndTime nvarchar(10)= '12:45'
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime)
How to I use Column names instead of Hard coding variables - e.g. '12:00'
June 3, 2015 at 7:57 am
Which column? From which table?
Please post table scripts, sample data end expected output.
See here for posting guidelines: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
-- Gianluca Sartori
June 3, 2015 at 8:05 am
I'm not 100% sure I know exactly what you are looking for, but if you're looking to compute the time difference between two fields that are stored as character fields instead of actual time or datetime values, then the method will depend on which of those scenarios you're working with.
WITH TIME_CHARS AS (
SELECT '1200' AS T1, '1245' AS T2
)
SELECT TC.T1, TC.T2, CAST(T2 AS int) - CAST(T1 AS int) AS DIFF_MINS
FROM TIME_CHARS AS TC;
WITH TIME_VALS AS (
SELECT CAST('12:00' AS time) AS T1, CAST('12:45' AS time) AS T2
)
SELECT TV.T1, TV.T2, DATEDIFF(mi, TV.T1, TV.T2) AS DIFF_MINS
FROM TIME_VALS AS TV;
One of the above two queries might help...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 3, 2015 at 8:08 am
sgmunson (6/3/2015)
I'm not 100% sure I know exactly what you are looking for, but if you're looking to compute the time difference between two fields that are stored as character fields instead of actual time or datetime values, then the method will depend on which of those scenarios you're working with.
WITH TIME_CHARS AS (
SELECT '1200' AS T1, '1245' AS T2
)
SELECT TC.T1, TC.T2, CAST(T2 AS int) - CAST(T1 AS int) AS DIFF_MINS
FROM TIME_CHARS AS TC;
WITH TIME_VALS AS (
SELECT CAST('12:00' AS time) AS T1, CAST('12:45' AS time) AS T2
)
SELECT TV.T1, TV.T2, DATEDIFF(mi, TV.T1, TV.T2) AS DIFF_MINS
FROM TIME_VALS AS TV;
One of the above two queries might help...
Did you actually read the question?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2015 at 8:23 am
You should not be using the arithmetic subtraction operator to calculate differences between date or time expressions. Subtract will only work with the old DATETIME and SMALLDATETIME data types. It will not work with the new date and time datatypes including TIME. You should be using the DATEDIFF() function to calculate differences between date and time expressions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2015 at 8:24 am
declare @StartTime nvarchar(10)= '12:00'
declare @EndTime nvarchar(10)= '12:45'
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime)
select EndTime, StartTime, @Diff from times;
-------------------------------------------------------------------------------------------------
So instead of hard coding the StartTime and EndTime values (e.g. 12:00, 12:45) like I have done above, I require to the above code to pull the rows (EndTime and StartTime) from the Times table and to work out the difference in times in the Diff column.
I don't want to use Temp tables or CTE's - as I won't to get the basics right first before I moved to advanced functions.
Thank you for your help.
June 3, 2015 at 8:28 am
patelxx (6/3/2015)
declare @StartTime nvarchar(10)= '12:00'declare @EndTime nvarchar(10)= '12:45'
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime)
How to I use Column names instead of Hard coding variables - e.g. '12:00'
You could use logic like
SET @StartTime = (SELECT TOP 1 StartTime_Column FROM YourTable WHERE .....)
OR
SELECT @EndTime = EndTime_Column FROM YourTable WHERE .....)
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2015 at 8:28 am
It would help, just a tad, if we had a clue what the definition of the Times table looks like and what the data in it consists of.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 3, 2015 at 8:30 am
Alvin Ramard (6/3/2015)
sgmunson (6/3/2015)
I'm not 100% sure I know exactly what you are looking for, but if you're looking to compute the time difference between two fields that are stored as character fields instead of actual time or datetime values, then the method will depend on which of those scenarios you're working with.
WITH TIME_CHARS AS (
SELECT '1200' AS T1, '1245' AS T2
)
SELECT TC.T1, TC.T2, CAST(T2 AS int) - CAST(T1 AS int) AS DIFF_MINS
FROM TIME_CHARS AS TC;
WITH TIME_VALS AS (
SELECT CAST('12:00' AS time) AS T1, CAST('12:45' AS time) AS T2
)
SELECT TV.T1, TV.T2, DATEDIFF(mi, TV.T1, TV.T2) AS DIFF_MINS
FROM TIME_VALS AS TV;
One of the above two queries might help...
Did you actually read the question?
More than once. It's not clear to me whether the original poster just needs to substitute a field name in directly, which, if true, would be kind of obvious with any knowledge of variables, so I'm thinking that there's something else going on here. I posted those two queries to provide guidance based on a couple of possibilities for what the original poster actually needs help with. If you came to a different conclusion, why not recognize that my conclusion about the original poster's intent and yours are different instead of asking me whether or not I read the question. You and I arguing over interpretation isn't going to help anyone...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 3, 2015 at 8:33 am
oops
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2015 at 8:40 am
patelxx (6/3/2015)
declare @StartTime nvarchar(10)= '12:00'declare @EndTime nvarchar(10)= '12:45'
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime)
How to I use Column names instead of Hard coding variables - e.g. '12:00'
Are you trying to do a report? Function? We know you don't want to hard code your variables but we need context. It's possible that you don't even need variables at all. If you simply want to get the time difference between two columns in a table...something like this would do.
SELECT
StartTime,
EndTime,
DATEDIFF(mi, StartTime, EndTime) AS Diff
FROM
myTimeTable
But this could be completely wrong, we are just taking shots in the dark. Don't be discouraged we are truly trying to help but just need more details.
Cheers,
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply