Truncating timestamp in "datetime" datatype

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)

Truncating timestamp in "datetime" datatype

Author

Topic

user9999
Starting Member

2 Posts

Posted - 2010-10-25 : 15:54:18

Hey guys,

How do i truncate timestamp for the column ? I m totally new to sql server DB ? I m an oracle guy..i know how to do in PL/SQL ...but how do that conversion in TSQL

BTW i m using sql server 2000

say i have

create test_table (
col1 datetime )

test_table
------------
12/8/2010 12:00:00

i want something like this

test_table
----------
12/8/2010

Thank you!!

mfemenel
Professor Frink

1421 Posts

Posted - 2010-10-25 : 16:07:51

if you just want to trim it off you can convert it to a varchar.
declare @mydate datetime
set @mydate = getdate()

select @mydate

select convert(varchar(20),@mydate,101)

Mike
"oh, that monkey is going to pay"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-25 : 16:47:22

Converting to varchar works, but it's one of the most expensive ways to do this. http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/

If you're doing it for display purposes, use CONVERT with the appropriate format code. If you're trimming the date off for storage or further calculations, use one of the faster ways.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources