Get time in hh:mm format

Question

Post reply

Get time in hh:mm format

rajesh_bhirud111

Old Hand

Points: 308
More actions
May 7, 2008 at 10:19 pm

#207125

Hi all,
I have to transform FoxPro table in SQL database having fields 'startime' & 'endtime' of character type storing the time in the format "hh.mm".
Please help me to convert this format in datetime format, so that I can get the data in "hh:mm" format in the SQL table.
Ex.
starttime endtime
07:00 08:40
16:10 20:00
Thanks in advance.
Regards
Rajesh

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

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

Zahran SSCommitted Points: 1724 More actions · Answer 1

Save the time in DATETIME data type in you database. When you retrieve you may convert it to the format you mentioned.

[font="Courier New"]DECLARE @date DATETIME

DECLARE @time CHAR(5)

SET @time = '07:45'

SET @date = CONVERT(DATETIME,@time)

SELECT @date

SELECT LEFT(CONVERT(VARCHAR,@date,108),5)[/font]

- Zahran -

xanthos SSCrazy Points: 2510 More actions · Answer 2

You might want to replace the dot in the time columns.

DECLARE @date DATETIME

DECLARE @time CHAR(5)

SET @time = '07.45'

SET @date = CONVERT(DATETIME,REPLACE(@time, '.', ':'))

SELECT @date

SELECT LEFT(CONVERT(VARCHAR,@date,108), 5)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

xanthos (5/9/2008)
You might want to replace the dot in the time columns.
DECLARE @date DATETIME
DECLARE @time CHAR(5)
SET @time = '07.45'
SET @date = CONVERT(DATETIME,REPLACE(@time, '.', ':'))
SELECT @date
SELECT LEFT(CONVERT(VARCHAR,@date,108), 5)

These guys have hit the nail on the head. When you move the data over you should put the time in a datetime column, unless you are using SQL 2008.

There is no need to use the left function to grab the time, in the format you want. This is an unnecessary step. Simply convert your datetime column to varchar(5).

e.g.

declare @dt datetime

set @dt = '07:30'

select convert(varchar(5),@dt,108)

I would also like to point out that any times that get moved over will have a date of 1/1/1900. I do not know how you are handling the dates for these times values, but it would be best if you brought them over with the time, in the same column.

My blog: http://jahaines.blogspot.com

rajesh_bhirud111 Old Hand Points: 308 More actions · Answer 4

Hi all,

Thanks for contiributing the knowledge.

My real problem is that I just want to replace the period '.' with colon ':'

Let me explain the fields;

Field Type(length)

-------- ------------

starttime varchar(5)

endtime varchar(5)

Number of records are more that 3.25 lakhs

Please help me asps.

Regards,

Rajesh

xanthos SSCrazy Points: 2510 More actions · Answer 5

Rajesh,

You've said it! Use the "replace"-function:

select replace(Starttime', '.', ':')

xanthos SSCrazy Points: 2510 More actions · Answer 6

Sorry that's 1 quote too many:

select replace(Starttime, '.', ':')