Removing the time from a datetime database field

Question

Removing the time from a datetime database field

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

If you cast directly to int depending on the time portion of the datetime value you may get different DATES but , if you really need to do it, you can use the workaround that Frank posted above!

* Noel

srankin Say Hey Kid Points: 703 More actions · Answer 2

Simple.

select CAST(FLOOR(CAST(GETDATE() as float)) as datetime)

This:

1: Converts a datetime to a floating point number

2: Truncates the decimal postion (time) of the floating point number without changing the value of the integer portion

3: Converts the floating point number back into a datetime

This can be used anywhere you'd like.  No need to create a UDF or anything of the sort.  There may be faster ways (cpu cycles) of doing it but the total time you'd save you've wasted on this discussion thread.

linto SSC Eights! Points: 851 More actions · Answer 3

use DATEPART function and retrieve the date parts from the date retrieved.

declare @dtTime datetime

set @dtTime = getdate()

SELECT CAST(DATEPART(m, @dtTime)as varchar) +'/'+ CAST(DATEPART(d, @dtTime)as varchar) +'/'+ CAST(DATEPART(yy, @dtTime)as varchar)

Linto

Phil Parkin SSC Guru Points: 246959 More actions · Answer 4

Noeld – please explain why you should not cast a datetime as int. I have used

cast(cast(GETDATE() - 0.5 as integer) as DateTime)

with apparent success. Just interested ... the FLOOR solution already mentioned seems more elegant anyway.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

As I alluded to in my original posting...

Casting (or Converting) the date as INT or BIGINT may give you the wrong (next day) date because dates with times of 12:00 or later will be rounded up to the next day automatically. Don't use any form of INT.

Further, any type of conversion to DECIMAL will cause similar errors due to the automatic rounding inherent in the datatype.

The idea of a UDF (User Defined Function) is a great one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Could be the cache had enough data to perform without reads and the cost could go down. I suggest trying multiple times and see what happens. However thare could be other factors, would have to see the execution plan.

Jason Lieber SSC Rookie Points: 39 More actions · Answer 7

Jason Lieber

SSC Rookie

Points: 39

February 26, 2004 at 8:56 am

#495887

Thanks for all of your help everyone.

nap_parikh SSC Veteran Points: 209 More actions · Answer 8

I agree with Antares686 suggestion. but one another way for comparision is to use convert function.

convert(varchar(10),DateTime,101) that will return only date and remove time and you can compare with your another date field.