# Miscellaneous > General Database Discussions >  truncate the time portion in datetime stamp

## ndba

Hi all:
first of all, i must say that this website is just awesome...

my question is how do i truncate the time portion in a datetime stamp in a single sql statement. 

thanks.

----------


## rmiao

Can use datepart function, check books online for details.

----------


## ndba

Yes, currently i am using the datepart function to get what I need..I was just wondering if there was a different way to do it.

Thanks

----------


## Stephen

Have a look at functions CONVERT and CAST.  

e.g. SELECT CONVERT (VARCHAR, GETDATE(), 112)

will give you something like 20030402

Check the other "style" values in books on line to see which is the closest match to your desired result

----------


## Wobin

There's also
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

which will also get the job done, and seems to be somewhat marginally faster. Works through side effects of data conversion, however...

----------


## bambola

> _Originally posted by Wobin_ 
> *There's also
> SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))*



select dateadd(dd, datediff(dd, 0, getdate()), 0)

This should be fast and more accurate than the float conversion.

Bambola.

----------


## idle_ike_2

I just had to research this myself as well.

A datetime is essentially the same as a float. 
So in a better world
  select floor(getdate())
would truncate to midnight.

However, MS in their infinite wisdom does not allow implicit translation between datetime and float.
So you have to convert it, then floor it, then convert it back to a datetime
hence
  SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))

These all do the same thing
  convert(datetime,convert(int,getdate()-0.5)),
  convert(datetime,floor(convert(float,getdate()))),
  cast(cast(getdate()-0.5 as int) as datetime),
  cast(floor(cast(getdate() as float)) as datetime)

This isn't a 'side effect' of convert(), it's just manipulation of numbers.
Using the datediff and dateadd method is not more accurate or faster, but some may be more comfortable with it.

I ran some tests and
  cast(cast(getdate()-0.5 as int) as datetime)
or
  convert(datetime,convert(int,getdate()-0.5))
are 5% faster than using floor()
Using datediff and dateadd takes  exactly the same time as the floor() method.

My 6 bits worth.

Ike

----------


## MAK

select  left(getdate(),11)

----------


## idle_ike_2

that returns a varchar, not a datetime

----------


## MAK

select convert(datetime,left(getdate(),11))

----------

