Derived 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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Derived column

Author

Topic

krabople
Starting Member

17 Posts

Posted - 2008-05-06 : 11:42:37

Hi, wondering if anyone can help me. I currently have a field that has a date and time in it in the format dd/mm/yyyy hh:mm:ss. Ideally I would like to get rid of the time part of it altogether but for it to still be recognised as a date as opposed to a string. However, I've been told that this is not possible in 2005, is this true? If this is the case, what would be the best way to set the time to 00:00:00 after the date for all records on that field?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:52:43

Create a Calculated column

create table #temp (dt datetime)

insert #temp
select getdate()

select *
from #temp

alter table #temp
add MyDateWithoutTime As (DATEADD(DAY, DATEDIFF(DAY, '19000101', dt), '19000101'))

select *
from #temp

drop table #temp

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources