Date format issue - 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

General SQL Server Forums

New to SQL Server Programming

Date format issue

Author

Topic

ebi007
Starting Member

15 Posts

Posted - 2012-10-25 : 10:35:18

hi all,

i would like to get difference between two given dates,
'2012-10-25 17:14:40','2012-10-25 19:01:01'
i need the output in the following format HH:MM:SS.

my following script does not work,what's wrong ?


DECLARE @datex DATETIME
DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SELECT @date1 = CONVERT(DATETIME,'2012-10-25 17:14:40')
SELECT @date2 = CONVERT(DATETIME,'2012-10-25 19:01:01')
SELECT @datex = DATEDIFF(dy,@date1,@date2)
SELECT CONVERT(VARCHAR(8),@datex,108)

thanks for helping

---------------------------
there is no limits only God can stop me.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-25 : 10:50:05

What do you mean when you say it doesn't work.
Does it give an error - if so what error.
Does it give an incorrect result - if so what result do you expect.

If you convert to a date with that format then you should give a style as it is ambiguous.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ebi007
Starting Member

15 Posts

Posted - 2012-10-25 : 10:58:21

There are no errors,the problem is i get unexpected result
something like 00:00:00.
i should have something different from that.
for instance 02:55:04 (this not the correct answer)

---------------------------
there is no limits only God can stop me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:18:39

thats because you're looking for only day difference, make it like


DECLARE @datex int
DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SELECT @date1 = CONVERT(DATETIME,'2012-10-25 17:14:40')
SELECT @date2 = CONVERT(DATETIME,'2012-10-25 19:01:01')
SELECT @datex = DATEDIFF(ss,@date1,@date2)
SELECT CONVERT(VARCHAR(8),DATEADD(ss,@datex,0),108)

if its goes beyond day part you need to separate out days and add it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ebi007
Starting Member

15 Posts

Posted - 2012-10-25 : 11:30:51

thank you all guys for helping...

---------------------------
there is no limits only God can stop me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:37:39

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources