Date Time concatination and conversion

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)

Date Time concatination and conversion

Author

Topic

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2006-12-12 : 20:35:14

I have a table with a column named Activity_Date that is set as type datetime and a column named Activity_Time which is an nvarchar type.

Activity_Date datetime 8
Activity_Time nvarchar 50
Activity_Date_Time datetime 8

I would like to take the Activity_Date, append the Activity_Time, and write the combined date and time to the Activity_Date_Time column.

A related question. How can I convert the Activity_Time column to a datetime data type and preserve the times that are in it? Is this possible?

A third question, at present the Activity_Time column is an nvarchar type, so when it is used in an ORDER BY Activity_Time it does not know that 1:30 AM is before 1:00 PM. Is there a way to convert the field to datetime for use only by the ORDER BY clause so that it puts time in the correct order?

example:

Select * FROM Activities ORDER BY Activity_Date ASC, Activity_Time ASC

Thanks,

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-12 : 23:31:19

1. Combine date and time:

declare @dt datetime,
		@tm datetime
select @dt = '01/02/2006', @tm = '12:01:49'
select dateadd(day, datediff(day,@tm, @dt), @tm)

2. Yes, when you convert to Datetime data type, time will be preserved
3.

order by convert(datetime, Activity_Time, 108)

Note: Do not store Datetime in the varchar/nvarchar/char columns in the first place.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-13 : 20:12:01

And, you don't need to make a physical column in the table to hold Activity_Date_Time... it can be a calculated column that does the concatenation and conversion without an impact on performance and without having to worry about updating the column all the time for new/updated entries in the two source columns.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources