changing to time format - 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

changing to time format

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-03 : 07:31:52

I have a table with values

empno timein

1 9.30
2 7
3 5.20

time in is in 9.30 format can we change it to 09:30:00 format

if yest please help thanx in advance

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 07:43:05

SELECT CONVERT(time, '9:30')

if your "9.30" is a String then

SELECT CONVERT(time, REPLACE(timein, '.', ':'))

if your "9.30" is a FLOAT number then

SELECT CONVERT(time, REPLACE(CONVERT(varchar(20), timein), '.', ':'))

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-03 : 07:47:54

You spent a lot of time asking how to get time INTO decimal, now you want to put it back to time?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154373
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154101
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153792
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153956
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153794

Poor planning on your part does not constitute an emergency on my part.

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-03 : 08:43:40

i have tried this
convert(varchar(10),replace(tr_id,'.',':'))

but if timein data is
9.30
7
8
5.30

with convert we can do for 9.30 and 5.30 but for 7 and 8 how it will work

thanx in advance

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-03 : 08:45:46

i have used varchar because when i am using date its giving error

Conversion failed when converting date and/or time from character string.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 08:56:54

or

declare @time varchar(10)
set @time ='9.30'

SELECT CONVERT(VARCHAR(12),CONVERT(DATETIME, replace(@time,'.',':')),108)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 11:29:37

quote:
Originally posted by pnasz

i have tried this
convert(varchar(10),replace(tr_id,'.',':'))

but if timein data is
9.30
7
8
5.30

with convert we can do for 9.30 and 5.30 but for 7 and 8 how it will work

So what was wrong with what I suggested then?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-03 : 23:57:08

hi pnasz, if the data input you are providing is having "." in the timing information then the Query Specified by Kristen will work fine and will give you the desired result. However, the case like 8, 9 etc will give an error ... so use the Case Statement for this purpose ... e.g.

Select
case
when Charindex('.',@timein)=0 then Convert(time,replace(@timein+'.00','.',':'))
When Charindex('.',@timein)>0 Then Convert(time,replace(@timein,'.',':'))
End as Timein

Hope this will work for you ..

Cheers!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 01:56:43

<<
so use the Case Statement for this purpose
>>

Note that CASE is an expression

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 02:40:55

"However, the case like 8, 9 etc will give an error"

Ah, good point, thanks. I hadn't considered that single digit would not be implicitly convertable to TIME.

There are probably further data values that will give trouble - such as "9."

As always, the best advice is to store such data as a TIME or DATATIME in the first place

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 03:24:57

Thanks Madhivanan for the rectification. However need a help from you too over a question ... which is posted on the hot topic "Script Library - Export to Excel" :)

@Kristen, Yes you are correct the missing case you mentioned, would not be catered in my query ... and thanks to you too :)

Many Thanks ...
Cheers!
MIK

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 04:38:58

"the missing case you mentioned, would not be catered in my query"

It just goes to show that the real problem here is using the wrong datatype in the first place. There are no doubt other "gotchas" on data type.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 04:48:03

<<
However need a help from you too over a question ... which is posted on the hot topic "Script Library - Export to Excel" :)
>>

Done

Madhivanan

Failing to plan is Planning to fail

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-04 : 07:27:13

i have tried this and its working fine but if data is 8.1 it is showing or getting converted to 8.01

use this formula for data 8.1

Select
case
when Charindex('.',@timein)=0 then Convert(time,replace(@timein+'.00','.',':'))
When Charindex('.',@timein)>0 Then Convert(time,replace(@timein,'.',':'))
End as Timein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 07:31:37

Have you tried this?

declare @time varchar(10)
set @time ='8.1'

SELECT CONVERT(VARCHAR(12),CONVERT(DATETIME, replace(@time,'.',':')),108)

Madhivanan

Failing to plan is Planning to fail

pnasz
Posting Yak Master

101 Posts

Posted - 2011-01-04 : 07:35:42

still getting wrong answer it should show 8.10 instead of 8.01

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-04 : 07:41:13

quote:
Originally posted by pnasz

still getting wrong answer it should show 8.10 instead of 8.01

This type of logic requirement would imply that 9.50 should become 9.05?

WHy is 8.1 supposed to become 8.01?

Poor planning on your part does not constitute an emergency on my part.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 08:24:46

"still getting wrong answer it should show 8.10 instead of 8.01"

It just goes to show that the real problem here is using the wrong datatype in the first place. There are no doubt other "gotchas" on data type.</repeat

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 08:34:54

Please note that this is how the SQL handles the conversion e.g.
8.1 converted into 8:01
and
8.10 converted into 8:10

If you provide a string with two decimal point then the conversion will simply convert it into time format ... if its one decimal point then it will be converted into Minutes with a 0 at the left side...

In order to cope with this introduce some more check in the case impression .. or prepare the data in excel with a pre-define generic format as per default conversion rules of SQL server...

Cheers!
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-04 : 08:39:45

pardon for a typo: the case impression --> should be read as --> the case expression :)

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 08:53:37

@MIK_2008 - in case you are not aware of it the

ICON will let you re-edit your own messages