Datetime2 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 2008 Forums

Transact-SQL (2008)

Datetime2 to time

Author

Topic

Jerremy09
Starting Member

4 Posts

Posted - 2011-11-07 : 01:27:21

Hi,

I would need to take time out from datetime2.

Example
Table:
ID Datetime Time
1 2011-11-5 20:34:54
2 2011-11-4 21:21:39
3 2011-11-6 10:09:08
4 2011-11-6 23:51:07

Now I want to take time out from column Datetime and set it in column Time. Table should look like this:

Table:
ID Datetime Time
1 2011-11-5 21:34:54 21:34:54
2 2011-11-4 15:21:39 15:21:39
3 2011-11-6 10:09:08 10:09:08
4 2011-11-6 23:51:07 23:51:07

Now if I create ordinary Select statement with "Order by Time", table should be ordered this way:

Table:
ID Datetime Time
3 2011-11-6 10:09:08 10:09:08
2 2011-11-4 15:21:39 15:21:39
1 2011-11-5 21:34:54 21:34:54
4 2011-11-6 23:51:07 23:51:07

There is no point of order table by DAtetime because it will order table according to DAY. I need to order table just according to time of the day. Coul you please help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-07 : 01:58:05

you can use convert(time, datetime_col) to convert to time.

Depending on your requirement, if it is just for ordering the records during retrieving, you can


select *
from   yourtable
order by convert(time, datetime_col)

or if you wish to have a column in the table, you can use computed column


alter table <table name> add time_col as convert(time, datetime_col)

KH
[spoiler]Time is always against us[/spoiler]

Lewie
Starting Member

42 Posts

Posted - 2011-11-07 : 02:04:38

declare @t1 table
(abc char(1),
T2 datetime2)

insert into @t1
select 'A',getdate()

insert into @t1
select 'B',getdate() + 1

insert into @t1
select 'c',getdate() - 1

insert into @t1
select 'D',getdate() + 2

insert into @t1
select 'E',getdate() - 2

select *
from @t1
order by CAST(T2 as time(7))

Jerremy09
Starting Member

4 Posts

Posted - 2011-11-07 : 13:54:44

Hi, thank you for replyes. I made it by "convert(time, datetime_col)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources