Convert Number To Time - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert Number To Time

Author

Topic

mahsa_mr
Starting Member

22 Posts

Posted - 2008-10-21 : 08:29:49

Hi,
i want to convert data in char :

13.05
18.42,
19.08

to time and the find diff between time:
for example: diff between 19.08 and 5.58 should become 13.10.

plaese help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 08:48:15

declare @char1 char(5), @char2 char(5)
select @char1='19.08',@char2='5.58'
select dateadd(day,0,replace(@char1,'.',':'))-dateadd(day,0,replace(@char2,'.',':'))

Madhivanan

Failing to plan is Planning to fail

mahsa_mr
Starting Member

22 Posts

Posted - 2008-10-21 : 08:53:48

Thanks a lot , and how can i Filer to just show the time ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:02:13

You should always use proper DATETIME datatype and let the front end do the formation

declare @char1 char(5), @char2 char(5)
select @char1='19.08',@char2='5.58'
select convert(varchar(10),dateadd(day,0,replace(@char1,'.',':'))-dateadd(day,0,replace(@char2,'.',':')),108)

Madhivanan

Failing to plan is Planning to fail

mahsa_mr
Starting Member

22 Posts

Posted - 2008-10-21 : 09:56:11

It is work untill format is in xx.xx , if format of data is xx
like 18 a get :Syntax error converting datetime from character string error.

mahsa_mr
Starting Member

22 Posts

Posted - 2008-10-21 : 09:56:12

It is work untill format is in xx.xx , if format of data is xx
like 18 a get :Syntax error converting datetime from character string error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 10:17:34

select convert(varchar(10),datediff(dateadd(ss,LEFT(@char1,2)*60+CASE WHEN CHARINDEX('.',@char1)>0 THEN RIGHT(@char1,2)*1 ELSE 0 END,0),dateadd(ss,LEFT(@char2,2)*60+CASE WHEN CHARINDEX('.',@char2)>0 THEN RIGHT(@char2,2)*1 ELSE 0 END,0)),108)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 03:07:43

quote:
Originally posted by visakh16

select convert(varchar(10),datediff(dateadd(ss,LEFT(@char1,2)*60+CASE WHEN CHARINDEX('.',@char1)>0 THEN RIGHT(@char1,2)*1 ELSE 0 END,0),dateadd(ss,LEFT(@char2,2)*60+CASE WHEN CHARINDEX('.',@char2)>0 THEN RIGHT(@char2,2)*1 ELSE 0 END,0)),108)

It gives error

Msg 174, Level 15, State 1, Line 5
The datediff function requires 3 argument(s).

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-22 : 03:11:45

quote:
Originally posted by mahsa_mr

It is work untill format is in xx.xx , if format of data is xx
like 18 a get :Syntax error converting datetime from character string error.

If it is of xx format append extra zeros to have xx.xx format

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 03:50:37

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

select convert(varchar(10),datediff(dateadd(ss,LEFT(@char1,2)*60+CASE WHEN CHARINDEX('.',@char1)>0 THEN RIGHT(@char1,2)*1 ELSE 0 END,0),dateadd(ss,LEFT(@char2,2)*60+CASE WHEN CHARINDEX('.',@char2)>0 THEN RIGHT(@char2,2)*1 ELSE 0 END,0)),108)

It gives error

Msg 174, Level 15, State 1, Line 5
The datediff function requires 3 argument(s).

Madhivanan

Failing to plan is Planning to fail

i had missed first argument for datediff. thanks for spotting it out

DECLARE @char1 varchar(5),@char2 varchar(5)
SELECT @char1='00.58',@char2='13.38'

select convert(varchar(10),datediff(ss,dateadd(ss,LEFT(@char1,2)*60+CASE WHEN CHARINDEX('.',@char1)>0 THEN RIGHT(@char1,2)*1 ELSE 0 END,0),dateadd(ss,LEFT(@char2,2)*60+CASE WHEN CHARINDEX('.',@char2)>0 THEN RIGHT(@char2,2)*1 ELSE 0 END,0))/60) + '.'+Convert(varchar(10),datediff(ss,dateadd(ss,LEFT(@char1,2)*60+CASE WHEN CHARINDEX('.',@char1)>0 THEN RIGHT(@char1,2)*1 ELSE 0 END,0),dateadd(ss,LEFT(@char2,2)*60+CASE WHEN CHARINDEX('.',@char2)>0 THEN RIGHT(@char2,2)*1 ELSE 0 END,0))%60)

output
-------------------
12.40

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-02 : 03:23:56

plesae check this :

declare @char2 char(5)
select @char2='5.5'
select dateadd(day,0,replace(@char2,'.',':'))

it, return :5:05 , it shoul be 5:50 , how can i fix it ?
Thank you

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-02 : 04:09:49

i found it

:
declare @char2 char(5)
select @char2='5.5'
select dateadd(day,0,replace((case when substring(@char2,5,1)='' then STUFF( @char2, 4, 1,0) else @char2 end ) ,'.',':'))

any idea ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 10:38:03

quote:
Originally posted by mahsa_mr

i found it :
declare @char2 char(5)
select @char2='5.5'
select dateadd(day,0,replace((case when substring(@char2,5,1)='' then STUFF( @char2, 4, 1,0) else @char2 end ) ,'.',':'))

any idea ?

wont this return NULL? you just have only 3 characters(5,.,5) in @char2 but you're using 4th character inside STUFF which will return NULL as result.

mahsa_mr
Starting Member

22 Posts

Posted - 2008-11-03 : 09:43:12

Actully i have a problem if time are in this format :

17.16
7.12
17.1
7.2

any help ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:51:41

was there any problem with my suggestion?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources