difference

Question

Post reply

difference

kumar99ms

SSCommitted

Points: 1584
More actions
October 15, 2008 at 12:20 pm

#194581

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

David O Hall of Fame Points: 3228 More actions · Answer 3

Once you get your formatting issues fixed the function is datediff. Check out Books on line for the details of the function.

seth delconte SSCertifiable Points: 6388 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)