how to seperate column? - 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

how to seperate column?

Author

Topic

kunaaldesai
Starting Member

36 Posts

Posted - 2009-07-25 : 18:41:39

Hello guys,
I have a time stamp in my table and I want to convert into two separate columns Date and time.

I have something like this 06/01/2009 00:00:00 and I want to make it like

Column 1 (Date) --> 06/01/2009
column2( Time) --> 00:00:00

can anybody help??

Kunal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-25 : 18:54:52

Unless you are using SQL 2008, you can use the DATE and TIME data type, the datetime data type will always contain date and also the time portion.
You can split the time using the query below however the date (Column1) will still contain the time but at 00:00:00


Column1 = dateadd(day, datediff(day, 0, datecol), 0),
Column2 = datecol - dateadd(day, datediff(day, 0, datecol), 0)

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

kunaaldesai
Starting Member

36 Posts

Posted - 2009-07-25 : 20:19:13

what do you mean by "unless you are using SQL Server 2008" because , I am using SQL Server 2008.

Anything

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-07-25 : 23:52:10

quote:
Originally posted by kunaaldesai

what do you mean by "unless you are using SQL Server 2008" because , I am using SQL Server 2008.

Anything

He meant that in 2008 you can use new datatypes available for date and time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 00:55:24

for 2008, it will be easier.

create the 2 new column and using DATE and TIME data type, and just assigned your datetime column to it


alter table <yourtable> add NewDateCol date, NewTimeCol time
go

update t
set    NewDateCol = <time stamp col>,
       NewTimeCol = <time stamp col>
from   <yourtable> t

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

kunaaldesai
Starting Member

36 Posts

Posted - 2009-07-26 : 02:49:11

Thanks Kh,
I am actually using 2008 and I tried the code you said. Its working very fine and am really thankful to you.

Just loved new features of sql server 2008.

Kunal

Anything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-26 : 02:53:20

you are welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources