Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

difference Expand / Collapse
Author
Message
Posted Wednesday, October 15, 2008 12:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 4:57 PM
Points: 74, Visits: 550
Hi Every body,

How can i find difference between these two could any please give some quire for this

14:12:43 CDT 06/14/2007

and

14:12:49 CDT 06/14/2007





Thanks
Post #586445
Posted Wednesday, October 15, 2008 12:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, February 21, 2014 7:54 AM
Points: 1,619, Visits: 1,233
The answer is 6 seconds. ;)

But seriously, what kind of date format is that? I couldn't find any examples of converting from that format.


_________________________________
seth delconte
http://sqlkeys.com
Post #586467
Posted Wednesday, October 15, 2008 12:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 7,121, Visits: 15,024
Same question, asked in a new package -

http://www.sqlservercentral.com/Forums/FindPost585845.aspx

and

http://www.sqlservercentral.com/Forums/FindPost585851.aspx

No matter what you do with these - you will need to convert them first, then do whatever math you need to do.

You REALLY should keep these all together, since they are so closely related. You give the impression you're not reading the answers you're being given.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #586468
Posted Wednesday, October 15, 2008 12:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:24 PM
Points: 176, Visits: 817
Once you get your formatting issues fixed the function is datediff. Check out Books on line for the details of the function.
Post #586469
Posted Wednesday, October 15, 2008 1:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, February 21, 2014 7:54 AM
Points: 1,619, Visits: 1,233
I agree, I think there's no way around the fact that you have to convert first. This should put you in the right direction for converting to standard MSSQL datetime format:

declare @oldDate varchar(50)
select @oldDate = '14:12:43 CDT 06/14/2007'

select convert(datetime,SUBSTRING(@oldDate,CHARINDEX(' ',@oldDate)+6,len(@oldDate))+' '+
SUBSTRING(@oldDate,0,CHARINDEX(' ',@oldDate)))


You can use a similar statement with an update or insert operation, then you will be free to use datediff or whatever to do the comparing!


_________________________________
seth delconte
http://sqlkeys.com
Post #586498
Posted Wednesday, October 15, 2008 7:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:06 PM
Points: 36,786, Visits: 31,243
David O (10/15/2008)
Once you get your formatting issues fixed the function is datediff. Check out Books on line for the details of the function.


Actually, once converted, you can just subtract one from the other and format it in the hh:mm:ss.mmm format (24 hour) using CONVERT. And, be careful using DATEDIFF for days... it can give you and extra day if the times are just right.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #586672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse