Get Only Time Part from 'DateTime' data type field

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 2000 Forums

Transact-SQL (2000)

Get Only Time Part from 'DateTime' data type field

Author

Topic

Kumar
Starting Member

2 Posts

Posted - 2004-09-12 : 14:14:36

Can you give me a SQL syntax or query to select only 'Time' portion from a table column which is defined as 'datetime'. ( 8 char length)

Also, I need to update this field (same table) with only time part.

e.g: '2004-04-15 17:45:30.0000' (existing value)
'05:45:30 PM' (Required)

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-12 : 14:32:25

Doesn't seem to be any style that gives that format without the milliseconds
select stuff(right('0'+ltrim(right(convert(varchar(26),getdate(),109),14)),14),9,4,' ')
That will truncate the milliseconds.

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-12 : 17:27:20

[code]declare @d datetime
set @d = '2004-04-15 17:45:30.000'

select
--@d,
case when datepart(hour,@d) < 12
then convert(varchar,@d,108) + ' AM'
else convert(varchar,dateadd(hour,-12,@d),108) + ' PM' end[/code]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-13 : 02:51:28

http://vyaskn.tripod.com/searching_date_time_values.htm

mk_garg

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-13 : 14:42:21

Looks like vyas doesn't cater for this format.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources