none
Calculating Difference between Times RRS feed

  • Question

  • I have a column with time recorded as 1645 for 4:45. I need to calculate the difference between this column and the current time. The only function I can use to retrieve the current time is getdate(). I've tried converting each column into integers to calculate the difference, but neither column would get above xx60, which means the difference will be off by up to 40. I can't use anything fancy in the solution; it must involve straight type conversions. Please help.
    Wednesday, June 17, 2009 8:16 PM

Answers

  • The following will return the current time without a date (well, in reality, the date is 1900-01-01, which is the "zero" date):

    select dateadd(day,datediff(day,getdate(),0),getdate())
    So the following will measure the difference in minutes between your time (16:45) and the current time:

    select datediff(minute,'16:45',dateadd(day,datediff(day,getdate(),0),getdate()))


    --Brad
    Wednesday, June 17, 2009 8:31 PM
    Moderator
  • Brad's approach at the solution is how I would try to solve this problem, based on the information presented.  Here are some varying versions that use the same logic:  You will need to add a from clause and specify your table name.  You will also need to use your column name and not the hardcoded value 1645.

    **Note: there are no concerns about times spanning accross differing days, right? if so, the solutions presented will not work.

    --method 1
    SELECT 
    	DATEDIFF
    	(
    		MINUTE,
    		CONVERT(DATETIME, STUFF(CONVERT(CHAR(8),1645),3,0,':')),
    		CONVERT(DATETIME,'1900/01/01 ' + CONVERT(VARCHAR(5),GETDATE(),108))	
    	)
    
    --method 2
    select 
    	DATEDIFF
    	(
    		MINUTE,
    		CONVERT(DATETIME,STUFF(CONVERT(CHAR(8),1645),3,0,':')),
    		dateadd(day,datediff(day,getdate(),0),getdate())
    	)

    http://jahaines.blogspot.com/
    Wednesday, June 17, 2009 9:01 PM
    Moderator

All replies

  • You can convert your integer value into a datetime value and then use regulare datetime functions like datediff(). 

    Converting it can be done something like this: first construct a string in the format {ts 'yyyy-mm-dd hh:mm:ss'}, then convert that into a datetime value. For example: 

    select convert(datetime, '{ts ''' + convert( varchar(10), getdate(), 120) + ' ' + right( '00' + convert( varchar(2), col / 100), 2) + ':' + right( '00' + convert( varchar(2), col % 100), 2) + ':00'''}', 120)
    • Edited by rrozema Wednesday, June 17, 2009 8:36 PM error in example
    Wednesday, June 17, 2009 8:31 PM
  • The following will return the current time without a date (well, in reality, the date is 1900-01-01, which is the "zero" date):

    select dateadd(day,datediff(day,getdate(),0),getdate())
    So the following will measure the difference in minutes between your time (16:45) and the current time:

    select datediff(minute,'16:45',dateadd(day,datediff(day,getdate(),0),getdate()))


    --Brad
    Wednesday, June 17, 2009 8:31 PM
    Moderator
  • Brad's approach at the solution is how I would try to solve this problem, based on the information presented.  Here are some varying versions that use the same logic:  You will need to add a from clause and specify your table name.  You will also need to use your column name and not the hardcoded value 1645.

    **Note: there are no concerns about times spanning accross differing days, right? if so, the solutions presented will not work.

    --method 1
    SELECT 
    	DATEDIFF
    	(
    		MINUTE,
    		CONVERT(DATETIME, STUFF(CONVERT(CHAR(8),1645),3,0,':')),
    		CONVERT(DATETIME,'1900/01/01 ' + CONVERT(VARCHAR(5),GETDATE(),108))	
    	)
    
    --method 2
    select 
    	DATEDIFF
    	(
    		MINUTE,
    		CONVERT(DATETIME,STUFF(CONVERT(CHAR(8),1645),3,0,':')),
    		dateadd(day,datediff(day,getdate(),0),getdate())
    	)

    http://jahaines.blogspot.com/
    Wednesday, June 17, 2009 9:01 PM
    Moderator