Split a Column Into Date and Time

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

Other SQL Server Topics (2005)

Split a Column Into Date and Time

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-08 : 13:22:46

Dear All,

I have the following data in ONE COLUMN:

9/14/2005 1:32:52 AM
9/14/2005 1:33:19 AM
9/14/2005 1:33:56 AM
9/14/2005 1:34:37 AM
9/14/2005 1:35:16 AM
9/14/2005 2:38:23 AM

I would like to split this into a DATE and a TIME column.
I believe I should use an Update command and it has to do with varchar.

I would appreciate help on this.

Thanks in Advance,
Dasman

SQL Should be Fun!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-08 : 13:30:14

If it's a VARCHAR data type:

SELECT	LTRIM(RTRIM(LEFT(dateColumn, charindex(' ' , dateColumn)))) as dt,
	LTRIM(RTRIM(RIGHT(dateColumn, len(dateColumn) - charindex(' ' , dateColumn)))) as tm
FROM	YourTable

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-08 : 13:56:36

Hello Russell

By dateColumn I assume u mean the column which has the date and time. My columnname for the data posted earlier is StartTime

So I ran this:

SELECT LTRIM(RTRIM(LEFT(StartTime, charindex(' ' , StartTime)))) as dt,
LTRIM(RTRIM(RIGHT(StartTime, len(StartTime) - charindex(' ' , StartTime)))) as tm
FROM TableName

and got this in TWO Columns:
dt tm
Sep| 14 2005 1:32AM
Sep| 14 2005 1:33AM
Sep| 14 2005 1:33AM
Sep| 14 2005 1:34AM
Sep| 14 2005 1:35AM
Sep| 14 2005 2:38AM

I actually wanted Sept 14 2005 in one column and 2:38 am in the other column.

Thanks,
Dasman

SQL Should be Fun!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-08 : 14:27:48

So the sample data you posted in the first place isn't what is actually in the table?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-08 : 14:32:37

You said you have this: 9/14/2005 1:32:52 AM

But apparently, you have this: Sep 14 2005 1:32AM

You can convert it to DATETIME, then use what I posted, or just use DatePart/DateAdd futonctions to parse it into date and time.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-08 : 15:15:42

Hey russell,

Sorry if i miscommunicated - here is the data column before query

9/14/2005 1:31:52 AM

then i ran ur query and got this in Two SEPARATE columns:
Sep 14 2005 1:31AM

Your query separated the month and converted it into letters.

SQL Should be Fun!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 21:08:01

[code]
select date_in_datetime = dateadd(day, datediff(day, 0, StartTime), 0),
time_in_str = convert(varchar(8), StartTime, 108)
[/code]

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

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-14 : 11:37:53

Hello All,

It was in fact Mr. Tan's code that gave me almost what I was looking for.

This did the deed:
Select Table1.StartTime,

date_in_datetime = convert(varchar(10), StartTime, 102),
time_in_str = convert(varchar(8), StartTime, 108)

From Table1

and I get this in TWO columns:
2005.09.14 01:31:52

from 2005-09-14 01:31:52.383

Thanks a lot everyone!

Best,
Dasman

==========================
Pain is Weakness Leaving the Body.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources